Displaying the Top or Bottom of a Range with TOP

Displaying the Top or Bottom of a Range with TOP

The TOP keyword displays only the top or bottom few records in a large record set. In queries, TOP is combined with a SORT clause to limit the number of records to a set number of records or a percentage of records in the result set.

For example, say that you want to view the three most recent outstanding orders in tblOrder. To do so, start by writing a SQL statement such as

 SELECT ID, OrderDate, CustomerID  FROM tblOrder ORDER BY OrderDate DESC 

The DESC keyword causes the result set to be sorted in descending (biggest to smallest) order. This query retrieves all the orders in tblOrder by customer, with the most recent order first and the earliest order last. This result is fine, except that in a database that stores every order received, you might have to sort thousands of records when all you're really interested in are the last three outstanding orders. So instead, try the SQL statement

 SELECT TOP 3 *  FROM tblOrder ORDER BY OrderAmount DESC 

This query retrieves the three records in tblOrder with the most recent order dates.

Note that, although you asked for three records, you're not guaranteed that only three records will be returned in this query. With a TOP N query, none, one, or two records may be returned if your table has only that many records. And if two or more records are tied for last place in your result list, four or more records may be returned.

There is no such thing as "BOTTOM N" in SQL syntax, but you can return the last few records in a table in this case, the most recent orders in your system. To create such a query, simply order the records by most recent date:

 SELECT TOP 3 *  FROM tblOrder ORDER BY OrderDate 

This query retrieves three records representing the three most recent orders in your database.

Sorting data in ascending order is implicit in SQL; there's no need to use the ASC keyword (to denote ascending sort order) unless you really want to.

Creating Top Percentage Queries

You can write queries that return a percentage of records in a table. For example, if you have a table with 1,000 records and you want to return the top 1 percent of records, 10 records will usually be displayed. Remember, though, that TOP N queries can be tricky more than 10 records may be displayed in a top percentage query if more than one record stores the same value.

To return the top records in a result set according to their percentage of the total records in your table, use the TOP N PERCENT clause. For example, to return the top 20 percent of outstanding orders in the tblOrder table, use

 SELECT TOP 20 PERCENT *  FROM tblOrder 

This query retrieves the two most recent orders, which is about what you'd expect from a table containing ten rows.