Microsoft SQL Server 2012 Bible

TOP ()

By definition, SELECT works with sets of data. Sometimes, however, it's only the first few rows from the set that are of interest. For these situations, SQL Server includes several ways to filter the results and find the top rows.

As mentioned earlier, SQL Server returns all the rows from the SELECT statement by default. The optional TOP() predicate tells SQL Server to return only a few rows (either a fixed number or a percentage) based upon the options specified (refer to Figure 6.4). A variable can be passed to TOP().

Note

The older syntax for TOP() did not include the parentheses and did not accept a variable. The newer syntax, with the parentheses, was introduced with SQL Server 2005 and is the best practice moving forward.

TOP() works hand-in-hand with ORDER BY. It's the ORDER BY clause that determines which rows are first. If the SELECT statement does not have an ORDER BY clause, then the TOP() predicate still works by returning an unordered sampling of the result set.

The AdventureWorks sample database is a good place to test the TOP() predicate. The following query finds the top 3 percent of product prices in the Product table. The Product lists all products and their corresponding prices:

USE AdventureWorks;
SELECT TOP(3) PERCENT
    ProductNumber, Name, ListPrice, SellStartDate
FROM Production.Product
ORDER BY ListPrice DESC

Result:

ProductNumber Name ListPrice SellStartDate ------------- -------------------- ---------- ----------------------- BK-R93R-62 Road-150 ...