O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required