LearnNowOnline

Blog

T-SQL: TOP Values

Excerpt by Don Kiely | April 29, 2013

A general best practice of data selection queries is that you never want to return more data from the database server than you're going to use. Even with a tightly focused WHERE clause, a query might still return more data than a user can make use of. You can further restrict the number of rows returned from a query using the TOP clause of a SELECT statement.

The TOP clause lets you limit the number of rows in a result set. For example, the following query selects the three cities with the highest number of employees, no matter how many cities might otherwise be returned from the query.

SELECT TOP 3

City, COUNT(*) AS NumEmployees

FROM dbo.Employees

GROUP BY City

ORDER BY COUNT(*) DESC;

The Figure below shows the result set from the query. A key element is the ORDER BY clause, which sorts the result set in descending order before the TOP clause is applied. (A sort in ascending order would return the three cities with the fewest number of employees.)

However, one problem with a TOP query is the matter of ties. In the previous query, several cities have only one employee. If you want to see the ties for last place, you need to use the WITH TIES clause:

SELECT TOP 3 WITH TIES

City, COUNT(*) AS NumEmployees

FROM dbo.Employees

GROUP BY City

ORDER BY COUNT(*) DESC;

The Figure below shows the result set. The query returns all three cities that tied for last place. For the data in this table, only a TOP 2 query will return unique values.

TOP also enables you to specify a percent value rather than an absolute number. Here's an example of using TOP with PERCENT to return the top 25% records. The results are shown in the Figure below.

SELECT TOP 25 PERCENT WITH TIES

City, COUNT(*) AS NumEmployees

FROM dbo.Employees

GROUP BY City

ORDER BY COUNT(*) DESC;

TIP: You can use any numeric expression, even a variable, to specify the number in a TOP clause. You can also use TOP in INSERT, UPDATE, and DELETE statements.

The GROUP BY clause in a SELECT statement is a powerful way to shape the data returned by a data selection query to perform basic data analysis. It might take some time to wrap your head around the set-based operation performed by non-trivial grouping, but it provides a very flexible way to wring information out of your data.

This post is an excerpt from the online courseware for our SQL Server 2012: T-SQL Select, Where, and Clauses course written by expert Don Kiely.