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

T-sql - we created Message from app1 and trying to disable from app2 (9h)

SQL select Top 10 records for unique combination of two columns (23h)

SSRS Report Sorting with Grouping Issue (2d)

ORA-01476: divisor is equal to zero (2d)

Create new columns based on min and max values of a record with multiple rows (2d)

Memory Required for Reporting Services 2022 (2d)

Backup sql server large db on cloud (2d)

Selecting x columns but only displaying y columns (2d)

- Advertisement -