What is Limit the sorted results

The TOP clause is a Microsoft-proprietary extension of the SELECT clause. TOP will let you specify how many rows to return, either as a positive integer or as a percentage of all qualifying rows. The number of rows can be specified as a constant or as an expression. TOP is most frequently used with an ORDER BY, but can be used with unordered data.

Using the TOP clause

The simplified syntax of the TOP clause, used with ORDER BY, is as follows:

SQLCopy

SELECT TOP (N) <column_list>
FROM <table_source>
WHERE <search_condition>
ORDER BY <order list> [ASC|DESC];

For example, to retrieve only the 10 most expensive products from the Production.Product table, use the following query:

SQLCopy

SELECT TOP 10 Name, ListPrice
FROM Production.Product
ORDER BY ListPrice DESC;

The results might look something like this:

Name

ListPrice

Road-150 Red, 62

3578.27

Road-150 Red, 44

3578.27

Road-150 Red, 48

3578.27

Road-150 Red, 52

3578.27

Road-150 Red, 56

3578.27

Mountain-100 Silver, 38

3399.99

Mountain-100 Silver, 42

3399.99

Mountain-100 Silver, 44

3399.99

Mountain-100 Silver, 48

3399.99

Mountain-100 Black, 38

3374.99

The TOP operator depends on an ORDER BY clause to provide meaningful precedence to the rows selected. TOP can be used without ORDER BY, but in that case, there is no way to predict which rows will be returned. In this example, any 10 orders might be returned if there wasn’t an ORDER BY clause.