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


Related Articles

Joining to the Next Sequential Row (2 April 2008)

Writing Outer Joins in T-SQL (11 February 2008)

How to Use GROUP BY with Distinct Aggregates and Derived tables (31 July 2007)

How to Use GROUP BY in SQL Server (30 July 2007)

SQL Server 2005: Using OVER() with Aggregate Functions (21 May 2007)

Server Side Paging using SQL Server 2005 (4 January 2007)

Using XQuery, New Large DataTypes, and More (9 May 2006)

Counting Parents and Children with Count Distinct (10 January 2006)

Other Recent Forum Posts

Select a single row based on conditions in multiple rows (3h)

I want Help Managing Big Data Sets in T SQL Efficiently (14h)

SQL stored procedure to load the error and correct record based on some business rules (1d)

Query is running too long (1d)

Sql Query to check status change of an item (1d)

Can I create differential backups tied to a specifc Full backup instead of the most recent? (7d)

My informix Sql query retruns Null always (8d)

Vehicle availability query (9d)

- Advertisement -