What's after TOP?

By Bill Graziano on 31 August 2000 | 14 Comments | 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 :)

Discuss this article: 14 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

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

VIEW (6 Replies)

Encryption and decryption of column values (3 Replies)

SQL Server 2008 on windows 8 (3 Replies)

Multiple criteria Rating (3 Replies)

Calculate lengths (7 Replies)

insert values of a column from one table to column (10 Replies)

Update values in two tables via stored procedure (4 Replies)

Update rows with increment date (19 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -