Tag Archives: SELECT statement

T-SQL: TOP Values

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.

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:

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.

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 is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.