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().

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;
    ProductNumber, Name, ListPrice, SellStartDate
FROM Production.Product


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

Get Microsoft SQL Server 2012 Bible now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.