Programmer's Rules

SQL Server

Using the TOP option to filter data in SQL Server Transact-SQL

Concise description of the SELECT TOP command in Transact-SQL

SQL Serverjob interviewTransact-SQL70-761

The TOP option allows to define a number of rows to get. You can specify an ordinal number (e. g. SELECT TOP 3) or a percentage (e. g. SELECT TOP 10 PERCENT).

The TOP option depends on the ORDER BY clause to provide intentional sequence of the rows selected. When there is no ORDER BY clause, there is no guarantee for which rows will be returned.

For example:

returns exactly 3 rows of the newest order date:

SalesOrderID CustomerID OrderDate
75084 11078 2014-06-30 00:00:00.000
75085 11927 2014-06-30 00:00:00.000
75086 28789 2014-06-30 00:00:00.000

As you will see, there are more rows in the table which conform to the newest order date, but they cannot be displayed because of the deterministic form of the TOP option version.

In order to return all rows of the newest orders that suites the condition: "select top 3 rows, but if there are more rows having order date equal to the last one, get those equal rows too." you should use TOP WITH TIES option.

Now the result is wider:

SalesOrderID CustomerID OrderDate
75084 11078 2014-06-30 00:00:00.000
75085 11927 2014-06-30 00:00:00.000
75086 28789 2014-06-30 00:00:00.000
75087 11794 2014-06-30 00:00:00.000
75088 14680 2014-06-30 00:00:00.000
75089 19585 2014-06-30 00:00:00.000

The following example shows how to use PERCENT option (it may be used with WITH TIES):

Tutorial statistics