Binaryworld - Extract records by their record number ... [ SQL Server -> T-SQL ]

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:

Click here to copy the following block

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

Click here to copy the following block

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.