What's after TOP?

By Bill Graziano on 31 August 2000 | Tags: SELECT

Lindsey writes "I know you can, quite easily, select the TOP 15 or 20 or 300 rows of a table, but is there a way to select 16-19? or 21-299? There doesn't seem to be one, but perhaps the Guru knows a secret trick..."

Ah, this guru does know a secret trick. Where one TOP is good, two TOPs are even better. I actually got to do this just the other day with a client. Suppose we have a table (Table1) with two columns - Id (the primary key) and Quantity (the value we're using for our TOP statement). If we want to just return the top 10 records sorted by Quantity we could code this:

SELECT TOP 10 Id
FROM Table1
ORDER BY Quantity DESCENDING

I'm assuming here that we want the highest quantities so we need to add the DESCENDING clause to our ORDER BY. Now if we want to return the second 10 we'll have to use a subquery. In our subquery we'll select the top 20 records and then in our main query we'll select the top 10 (or actually bottom 10) of those we just selected.

SELECT TOP 10 Id
FROM Table1
WHERE Id in (SELECT TOP 20 Id
  FROM Table1
  ORDER BY Quantity DESCENDING)
ORDER BY Quantity ASCENDING

Basically our subquery gets the 20 records with the highest quantity. The main query selects the TOP 10 of those records only it reverses the sort order so that it returns the bottom 10 records. Now, if you want to sort the final result set in descending order it gets tricky. The easiest approach is probably to put them into a temporary table and then sort them. Hope this helps :)