A query connundrum.
September 3, 2006 5:51 AM Subscribe
Attention SQL gurus
. This has me completely stumped. How can I find the index (position) of a row in a table without querying the entire set?
posted by Civil_Disobedient to computers & internet (12 answers total) 2 users marked this as a favorite
Here is the situation:
I have a web application that has some ajaxified tables on many of the pages. These tables show various slices of data. Most of the tables are about 10 rows per page, so for example, if the table was a list of all Customers, and you've got 10,000 customers, that would be 1,000 pages of customers.
To initially show the table, I have to do two queries: one is the initial 10 rows that are to be displayed, the other is a count(*) to get the total amount of rows.
Here is the problem:
I have a particular customer ID in my hand. I'd like the webtable to take this ID and jump to the correct page that will show that customer. Naturally, it has to take into account sorting.
Hypothetical simplified example:
1102 C_D's Tavern
1294 Bob's Discount Dairy
1299 Chico's House of Wicker
9309 Larry's Llama Emporium
If I sort by ID (natural order), the index of Bob's Discount Dairy would be 2, Chico's Hosue of Wicker would be 3, etc. If I'm displaying "page 1", that means the first 10 indexes, so I know those two would be on the first page. "OK, so what page is ID# 5129 on?" That's the problem.
What I want to know is if there's a way to figure out the index of a particular ID without having to query the entire dataset. Or if I do have to query the entire dataset, I'd like it to be as painless as possible. Another important note: some of these queries are being run off of VIEWs, which could complicate things.
This is on Microsoft SQL Server 2000.