Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SS7,SS2K
Expertise: Intermediate
Nov 8, 2001



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Extract records by their record number

SQL Server, and the SQL language in general, doesn't support record numbers, so you can't extract a set of records if you know their position in the resultset. This missing capability would be extremely convenient when displaying pages of data in an ASP program. For example, if each page contains 10 records, then displaying the 8-th page would mean displaying records from position 71 to position 80, where position depends also on the current sort order.

Even if the SQL language doesn't support this feature, you can simulate it - to an extent and for tables not too large, by using two nested SELECTs with the TOP clause, as in the following example based on the Titles table in the Pubs database:

-- get records 10-12 from table Titles, sorted on price
SELECT TOP 3 * FROM Table WHERE title_id IN
   (SELECT TOP 12 title_id FROM Titles ORDER BY price)
   ORDER BY price DESC
The above query returns records from 10 to 12 in the Titles table, when the table is sorted by price. The problem with this query is that the resultset is returned in reversed order, so you have to make it straight when processing the data. For example, if you're creating an HTML table, you must start by building the last row of cells and progress towards upper rows. If working with a reversed resultset is a problem - for example, when binding the result to a grid - you must resort to three nested SELECTs:
SELECT * FROM Titles WHERE title_id IN
    (SELECT TOP 3 title_id FROM titles WHERE title_id IN
       (SELECT TOP 10 title_ID FROM titles ORDER BY price)
     ORDER BY price DESC)
    ORDER By price
This technique is usually faster than reading the first N records until you get to the records you want to display, yet you can't use it for very large tables.
Francesco Balena
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date