Blog

T-SQL: TOP Values

Excerpt by Don Kiely

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.)

Using TOP functions

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 3 WITH TIES

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;

TOP 25 PERCENT

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.

Thumbnail for 566This 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.



Don Kiely

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor, and consultant specializing in Microsoft technologies. Don has many years of teaching experience, is the author or co-author of several programming books, and has spoken at many industry conferences and user groups. In addition, Don is a consultant for a variety of companies that develop distributed applications for public and private organizations.


This course excerpt was originally posted April 29, 2013 from the online courseware SQL Server 2012, Part 8 of 9: T-SQL Select, Where, and Clauses by Don Kiely

SQL Server