Row number

Row number

Post by MrZoidber » Fri, 30 May 2008 22:58:11


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!
 
 
 

Row number

Post by Paul G. To » Sat, 31 May 2008 00:05:33

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.

 
 
 

Row number

Post by MrZoidber » Sat, 31 May 2008 00:18:34

On 29 18:05, "Paul G. Tobey [eMVP]"<

>> 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!

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

 
 
 

Row number

Post by MrZoidber » Sat, 31 May 2008 00:51:37

On 29 , 18:37, "Ginny Caughey MVP"







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 =)
 
 
 

Row number

Post by Paul G. To » Sat, 31 May 2008 01:15:44

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]"<

>> 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!

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

 
 
 

Row number

Post by Ginny Caug » Sat, 31 May 2008 01:56:10

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"







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 =)
 
 
 

Row number

Post by MrZoidber » Sat, 31 May 2008 02:45:36

On 29 , 19:56, "Ginny Caughey MVP"










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.
 
 
 

Row number

Post by Ginny Caug » Sat, 31 May 2008 02:52:03

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"










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.
 
 
 

Row number

Post by Jo Paulo F » Sun, 01 Jun 2008 03:41:07

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 Paulo Figueira
DAD MVP