SQL Top Clause

The TOP clause is used to return a specified number of rows from the SELECT result.    It becomes more meaningful when used in conjunction with ORDER BY.  As then you’re able to find rows with top values. 

For instance, here are the ten highest products by list price. 

SELECT TOP 10 Name, ListPrice From Production.Product Order By ListPrice DESC; 
/* Answer */
SELECT TOP 10 Name, ListPrice 
From Production.Product 
Order By ListPrice DESC

What makes this work is that we use the DESC keyword to sort ListPrice in descending (9 to 0) order.  If case you’re wondering, there is no BOTTOM keyword.  To simulate this, just use an ORDER BY in ASC (Ascending) order. 

TOP Clause as Percent 

You can also use TOP to find a percentage of row.   For instance, to select the top five percent of all products, in terms of ListPrice, in the products table, write the following SQL:

SELECT TOP 5 PERCENT Name, ListPrice FROM Production.Product ORDER BY ListPrice DESC; 
/* Answer */
SELECT TOP 5 PERCENT Name, ListPrice 
FROM Production.Product 
ORDER BY ListPrice DESC

In summary you can use the TOP clause to return an absolute number of rows, or a percentage.  TOP gains more meaning used in conjunction with ORDER BY.  Here you’re able to select the TOP or “BOTTOM” values, depending on whether you sort your values in DESC (descending) or ASC (Ascending) order. 

Additional Resources 

To learn more about TOP, check out these useful resources: 

>