 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server CE / May 2008Row number
Hi! I'm using SQL Server Compact Edition 3.5 and I need to get row number in my query. How can I do this? Thanks for help! Maybe I'm thinking about this the wrong way, but what do you mean by "row number"? The table itself doesn't have row numbers, generally. Rows are assumed to be unordered. Your query can enforce an order on them, but that order is unrelated to the order of items physically in the data storage for the table. Paul T. > Hi! > > I'm using SQL Server Compact Edition 3.5 and I need to get row number > in my query. How can I do this? > > Thanks for help! On 29 май, 18:05, "Paul G. Tobey [eMVP]" <p space tobey no spam AT no instrument no spam DOT com> wrote: > Maybe I'm thinking about this the wrong way, but what do you mean by "row > number"? The table itself doesn't have row numbers, generally. Rows are [quoted text clipped - 10 lines] > > > Thanks for help! I have the query with ORDER clause and I what to see the row number in that query. Something like this: SELECT ROWNUM(), COL1, COL2 FROM TABLE1 ORDER BY COL1 As Paul says, SQL Compact doesn't have row numbers. Why do you think you need that? For example, if you need to update changes you make to the data selected and you want to find that row again, you'll use a primary key value to know which row in the database to update. Ginny Caughey Device Application Development MVP On 29 май, 18:05, "Paul G. Tobey [eMVP]" <p space tobey no spam AT no instrument no spam DOT com> wrote: > Maybe I'm thinking about this the wrong way, but what do you mean by "row > number"? The table itself doesn't have row numbers, generally. Rows are [quoted text clipped - 16 lines] > > > Thanks for help! I have the query with ORDER clause and I what to see the row number in that query. Something like this: SELECT ROWNUM(), COL1, COL2 FROM TABLE1 ORDER BY COL1 On 29 май, 18:37, "Ginny Caughey MVP" <ginny.caughey.onl...@wasteworks.com> wrote: > As Paul says, SQL Compact doesn't have row numbers. Why do you think you > need that? For example, if you need to update changes you make to the data [quoted text clipped - 32 lines] > Something like this: > SELECT ROWNUM(), COL1, COL2 FROM TABLE1 ORDER BY COL1 Thanks for the answer. I have the following problem. Some service insert the new row to the table. Then i have the virtual ListView control where that table data is shown. It has sorting (non-standard) and can have filtering. I need to know the row number of the inserted row in the ListView. I know the inserted row primary key and that the row number will be equal to the row number in the query. I really don't want to get all the table data and count rows by-hand. P.S. Sorry for my English =) How about ListView.Items.IndexOf() ? You can get the count of the number of rows in the listview with ListView.Items.Count. Ginny On 29 май, 18:37, "Ginny Caughey MVP" <ginny.caughey.onl...@wasteworks.com> wrote: > As Paul says, SQL Compact doesn't have row numbers. Why do you think you > need that? For example, if you need to update changes you make to the data [quoted text clipped - 38 lines] > Something like this: > SELECT ROWNUM(), COL1, COL2 FROM TABLE1 ORDER BY COL1 Thanks for the answer. I have the following problem. Some service insert the new row to the table. Then i have the virtual ListView control where that table data is shown. It has sorting (non-standard) and can have filtering. I need to know the row number of the inserted row in the ListView. I know the inserted row primary key and that the row number will be equal to the row number in the query. I really don't want to get all the table data and count rows by-hand. P.S. Sorry for my English =) On 29 май, 19:56, "Ginny Caughey MVP" <ginny.caughey.onl...@wasteworks.com> wrote: > How about ListView.Items.IndexOf() ? You can get the count of the number of > rows in the listview with ListView.Items.Count. [quoted text clipped - 58 lines] > > P.S. Sorry for my English =) Thak's all! It's good idea but my ListView can have up to 100.000 items and ListView.Items.IndexOf() will be retrieving all the data until the row is found. I think the performance will be very slow. Thank you guys, I realized I need to re-think my architecture. I agree, I think rearchitecting might be a good idea. You need something that is indexed to be able to quickly find newly added items of data, and the database has that capability already, so perhaps the service should add new items there instead of to a list view. Ginny On 29 май, 19:56, "Ginny Caughey MVP" <ginny.caughey.onl...@wasteworks.com> wrote: > How about ListView.Items.IndexOf() ? You can get the count of the number > of [quoted text clipped - 65 lines] > > P.S. Sorry for my English =) Thak's all! It's good idea but my ListView can have up to 100.000 items and ListView.Items.IndexOf() will be retrieving all the data until the row is found. I think the performance will be very slow. Thank you guys, I realized I need to re-think my architecture. I don't see any documented way of doing that in SQL Compact 3.5. I suppose that what you do next to try to fake this probably depends on how you're using the data that comes back from your query... Paul T. On 29 ÍÁÊ, 18:05, "Paul G. Tobey [eMVP]" <p space tobey no spam AT no instrument no spam DOT com> wrote: > Maybe I'm thinking about this the wrong way, but what do you mean by "row > number"? The table itself doesn't have row numbers, generally. Rows are [quoted text clipped - 16 lines] > > > Thanks for help! I have the query with ORDER clause and I what to see the row number in that query. Something like this: SELECT ROWNUM(), COL1, COL2 FROM TABLE1 ORDER BY COL1 Do you mean the row bookmark? On SQL Compact 3.5 this is a uint32 value that you can quickly retrieve (and use to seek) using the native OLE DB provider. Odds are you are using .NET, right? João Paulo Figueira DAD MVP > Hi! > > I'm using SQL Server Compact Edition 3.5 and I need to get row number > in my query. How can I do this? > > Thanks for help! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.