Microsoft® SQL Server® 2012 Bible

(Ben Green) #1

139


Chapter 6: Introducing Basic Query Flow


6


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
fi xed number or a percentage) based upon the options specifi ed (refer to Figure 6-4). A vari-
able 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 fi rst. 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 fi nds 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 Red, 62 3578.27 2005-07-01 00:00:00.000
BK-R93R-44 Road-150 Red, 44 3578.27 2005-07-01 00:00:00.000
BK-R93R-48 Road-150 Red, 48 3578.27 2005-07-01 00:00:00.000
BK-R93R-52 Road-150 Red, 52 3578.27 2005-07-01 00:00:00.000
BK-R93R-56 Road-150 Red, 56 3578.27 2005-07-01 00:00:00.000
BK-M82S-38 Mountain-100 Silver, 38 3399.99 2005-07-01 00:00:00.000
BK-M82S-42 Mountain-100 Silver, 42 3399.99 2005-07-01 00:00:00.000
BK-M82S-44 Mountain-100 Silver, 44 3399.99 2005-07-01 00:00:00.000
BK-M82S-48 Mountain-100 Silver, 48 3399.99 2005-07-01 00:00:00.000
BK-M82B-38 Mountain-100 Black, 38 3374.99 2005-07-01 00:00:00.000
BK-M82B-42 Mountain-100 Black, 42 3374.99 2005-07-01 00:00:00.000
BK-M82B-44 Mountain-100 Black, 44 3374.99 2005-07-01 00:00:00.000
BK-M82B-48 Mountain-100 Black, 48 3374.99 2005-07-01 00:00:00.000
BK-R89R-44 Road-250 Red, 44 2443.35 2006-07-01 00:00:00.000
BK-R89R-48 Road-250 Red, 48 2443.35 2006-07-01 00:00:00.000
BK-R89R-52 Road-250 Red, 52 2443.35 2006-07-01 00:00:00.000

The next query locates the six lowest prices in the product table:

USE AdventureWorks;
SELECT TOP(6)

c06.indd 139c06.indd 139 7/30/2012 4:16:08 PM7/30/2012 4:16:08 PM


http://www.it-ebooks.info
Free download pdf