Creative Ways to Use the TOP Clause

Have you ever wanted to return the last 100 rows of a given set of records? What about the second set of 100 records? Or possibly, you have wanted to return the first so many billing records for each billing month. This article will discuss how to use the TOP clause to help solve those requests where you want to restrict the number of records returned based on a record count. But first I will discuss the new features incorporated into the TOP clause with SQL Server 2005.

New Features in TOP Clause Implemented with SQL Server 2005

SQL Server 2005 brought a couple of new features to the TOP clause. The syntax of the TOP clause in SQL Server 2005 now looks like this:

TOP (expression) [PERCENT]
     [ WITH TIES ]

In SQL Server 2005 you are allowed to specify the number of rows you want to return with an expression, where as in prior versions you where only allowed to hard code a number. Being able to specify a variable allows you to more easily control the number of rows returned programmatically.

The second new feature is the “WITH TIES” options. This option instructs SQL Server to bring back additional rows should the last row have the same order by value as the next row. This option is only valid in a SELECT statement that contains an ORDER BY clause.

To demonstrate how to use these new TOP clause features let me go through a couple of examples.

Sample Data for Examples

Prior to going through my examples, I will need a table with some test data. Below is my sample data and a script to populate my sample TopDemo table:

set nocount on
create table TopDemo (
PaymentID int identity, 
Amount money, 
PayDate DateTime
)
insert into TopDemo(Amount, PayDate) values (12.23,'1/2/2006')
insert into TopDemo(Amount, PayDate) values (73.18,'1/10/2006')
insert into TopDemo(Amount, PayDate) values (92.20,'1/21/2006')
insert into TopDemo(Amount, PayDate) values (62.20,'1/21/2006')
insert into TopDemo(Amount, PayDate) values (12.45,'1/23/2006')
insert into TopDemo(Amount, PayDate) values (10.99,'2/4/2006')
insert into TopDemo(Amount, PayDate) values (34.23,'2/7/2006')
insert into TopDemo(Amount, PayDate) values (34.87,'2/12/2006')
insert into TopDemo(Amount, PayDate) values (58.32,'2/15/2006')
insert into TopDemo(Amount, PayDate) values (34.23,'2/23/2006')
insert into TopDemo(Amount, PayDate) values (98.26,'3/1/2006')
insert into TopDemo(Amount, PayDate) values (45.32,'3/15/2006')
insert into TopDemo(Amount, PayDate) values (47.30,'3/16/2006')
insert into TopDemo(Amount, PayDate) values (35.21,'3/24/2006')
insert into TopDemo(Amount, PayDate) values (57.23,'3/26/2006')
insert into TopDemo(Amount, PayDate) values (24.56,'3/30/2006')
insert into TopDemo(Amount, PayDate) values (34.23,'4/4/2006')
insert into TopDemo(Amount, PayDate) values (23.99,'4/6/2006')
insert into TopDemo(Amount, PayDate) values (56.32,'4/8/2006')
insert into TopDemo(Amount, PayDate) values (47.53,'6/25/2006')
insert into TopDemo(Amount, PayDate) values (30.56,'7/7/2006')
insert into TopDemo(Amount, PayDate) values (65.34,'7/8/2006')
insert into TopDemo(Amount, PayDate) values (44.55,'8/4/2006')
insert into TopDemo(Amount, PayDate) values (84.23,'8/8/2006')
insert into TopDemo(Amount, PayDate) values (23.56,'8/22/2006')
insert into TopDemo(Amount, PayDate) values (45.77,'9/12/2006')
insert into TopDemo(Amount, PayDate) values (65.90,'9/25/2006')

Using an EXPRESSION in the TOP Clause

SQL Server 2005 makes it easier to programmatically control the number of records you want to return when you use the TOP clause. With these changes to the TOP clause, you can now use a variable to identify the number of rows to return. Below is an example that returns the top 3 records from my TopDemo table:

declare @top int
set @top = 3
select top (@top) * from TopDemo

Here I defined an integer variable @top and set its value to 3. I then used that variable in the SELECT statement immediately following the TOP clause and enclosed it in parentheses. When the SQL Server 2005 engine executes this command, it evaluates the value of the variable @top to determine the number of rows to return. In my example, I return the following 3 records:

PaymentID   Amount                PayDate
----------- --------------------- -----------------------
1           12.23                 2006-01-02 00:00:00.000
2           73.18                 2006-01-10 00:00:00.000
3           92.20                 2006-01-21 00:00:00.000

Now if you have some code that determines the number of records you want to return, it is a simple matter of just getting that record number into a variable, then placing the variable into a TOP clause to constrain your returned record set to a specific number of records. Being able to dynamically control the number of rows returned in SQL Server 2005 means you no longer have to using dynamic SQL to accomplish this task.

Using the WITH TIES option

SQL Server 2005 also offers a WITH TIES option. This option allows you to specify that you want to return all the records that have the same value as last records returned by the TOP clause, based on the ORDER BY clause. Here is an example that demonstrates the WITH TIES option:

select top 3 with ties * from TopDemo 
order by PayDate

Here I am specifying that I want to return the top 3 records and any additional records that have the same PayDate as the third record. When I run this query this is my result set:

PaymentID   Amount                PayDate
----------- --------------------- -----------------------
1           12.23                 2006-01-02 00:00:00.000
2           73.18                 2006-01-10 00:00:00.000
3           92.20                 2006-01-21 00:00:00.000
4           62.20                 2006-01-21 00:00:00.000

As you can see, 4 records were returned. This is because the third and forth record have the same PayDate. So now, if you want to return all the records that have the same ORDER BY value, but still what to loosely constrain your returned set to a number of records, then the WITH TIES option should help.

Returning the Second Set of 4 Records

Some times, you don’t want to return the top set of so many records, but you want to return the next set of so many records. Here is an example that returns the second set of 4 records:

select top 4 * from TopDemo a 
 where PaymentID in (select top 8 PaymentID from TopDemo) 
    order by PaymentID desc

In order to return the second set of 4 records I used a correlated subquery (for more information on correlated subqueries click here). The correlated subquery returns “PaymentID” column values for the top 8 records. I then return only the first 4 matching TopDemo records that have one of the 8 PaymentID’s returned from the subquery. Since I requested my final set to be to be sorted in descending order by the PaymentID I will get the second set of 4 records. Here is the result set from the above query:

PaymentID   Amount                PayDate
----------- --------------------- -----------------------
8           34.87                 2006-02-12 00:00:00.000
7           34.23                 2006-02-07 00:00:00.000
6           10.99                 2006-02-04 00:00:00.000
5           12.45                 2006-01-23 00:00:00.000

As you can see, this set is ordered in descending sequence by PaymentID. If you really wanted the payments to be sorted sequentially by PaymentID, starting with the lowest PaymentID first, then you would have to run the following T-SQL statement, which sorts the final results set in ascending order by PaymentID:

select * from 
  (select top 4 * from TopDemo a 
   where PaymentID in (select top 8 PaymentID from TopDemo) 
   order by PaymentID desc) a
order by PaymentID

Here I just placed an alias named “a” around my original query’s result set, so I could re-sort my four records into PaymentID order.

How to get the BOTTOM 5 records

SQL Server does not supply a BOTTOM clause, but that doesn’t mean you can’t easily get the BOTTOM so many records. It is relatively simple to use the TOP clause to retrieve the bottom “x” number of records. Here is an example where I retrieved the bottom 5 records based on PayDate from my TopDemo table.

select top 5 * from TopDemo 
order by PayDate desc

In order to return the bottom five records all I had to do was specify the descending clause on my order by statement. This returned the bottom 5 records based on PayDate, although the final record set returned is ordered in descending order by PayDate. Below is the result set returned when I run the above query:

PaymentID   Amount                PayDate
----------- --------------------- -----------------------
27          65.90                 2006-09-25 00:00:00.000
26          45.77                 2006-09-12 00:00:00.000
25          23.56                 2006-08-22 00:00:00.000
24          84.23                 2006-08-08 00:00:00.000
23          44.55                 2006-08-04 00:00:00.000

Now normally you probably would want to return the bottom so many records and have those records be ordered based on a key in ascending order, instead of having them sort like my above results set where the PayDate is in descending order. This can be solved by wrapping another query around the above query, like so:

select * from (select top 5 * from TopDemo 
order by PayDate desc) a
order by PayDate

The above query will order my final result set in ascending PayDate order. Below is the result set of the above query:

PaymentID   Amount                PayDate
----------- --------------------- -----------------------
23          44.55                 2006-08-04 00:00:00.000
24          84.23                 2006-08-08 00:00:00.000
25          23.56                 2006-08-22 00:00:00.000
26          45.77                 2006-09-12 00:00:00.000
27          65.90                 2006-09-25 00:00:00.000

How to get the First Record of Each Month

For my last example, I will show you how you can use the TOP clause to return the first TopDemo payment record for each PayDate month. To accomplish this I will once again use a correlated subquery to identify the top payment record for each month, as the code below demonstrates:

select * from TopDemo a 
 where PayDate = (select top 1 PayDate from TopDemo 
    where month(PayDate) = Month(a.PayDate)
    order by PayDate) 
order by PayDate

Here the inner query (“select top 1…..”) a correlated subquery takes the month identification from the outer query to select the first PayDate for the that specific month. The first PayDate of the month is then used in the WHERE clause to return the first payment for each month. Below is the result set from the above correlated subquery:

PaymentID   Amount                PayDate
----------- --------------------- -----------------------
1           12.23                 2006-01-02 00:00:00.000
6           10.99                 2006-02-04 00:00:00.000
11          98.26                 2006-03-01 00:00:00.000
17          34.23                 2006-04-04 00:00:00.000
20          47.53                 2006-06-25 00:00:00.000
21          30.56                 2006-07-07 00:00:00.000
23          44.55                 2006-08-04 00:00:00.000
26          45.77                 2006-09-12 00:00:00.000

In my T-SQL code above, the inner query returns 8 different PayDate’s, one for the first payment date of each month that existed in my TopDemo table. These unique payment dates were then used in the outer query to return any TopDemo records that had one of those 8 payment dates.

Conclusion

The TOP clause is useful in helping you constrain your set to specific number of records. The new SQL Server 2005 features of the TOP clause now provides you with some alternatives for how to write your code for some specific data retrieve criteria. Being able to dynamically control the number of records returned is a big help when you don’t know exactly how many records you want to return. Now you can use the WITH TIES option as an easy method to return those records that have the same value as the last record in your TOP criteria.

» See All Articles by Columnist Gregory A. Larsen