Microsoft® SQL Server® 2012 Bible

140

Part II: Building Databases and Working with Data

ProductNumber, Name, ListPrice,

CONVERT(VARCHAR(10),SellStartDate,1) SellStartDate

FROM Production.Product

ORDER BY ListPrice DESC

Result:

ProductNumber Name ListPrice SellStartDate

-------------- --------------------- ------------- -------------

BK-R93R-62 Road-150 Red, 62 3578.27 07/01/05

BK-R93R-44 Road-150 Red, 44 3578.27 07/01/05

BK-R93R-48 Road-150 Red, 48 3578.27 07/01/05

BK-R93R-52 Road-150 Red, 52 3578.27 07/01/05

BK-R93R-56 Road-150 Red, 56 3578.27 07/01/05

BK-M82S-38 Mountain-100 Silver, 38 3399.99 07/01/05

The query looks clean and the result looks good, but unfortunately it’s wrong. If you look at

the raw data sorted by price, you can actually see fi ve rows with a price of 3578.27 and four

rows with a price of 3399.33. The WITH TIES option solves this problem, as described in

the following section.

Best Practice

By the nature of the formatting, computer-generated data tends to appear correct. Unit testing the

query against a set of data with known results is the only way to check its quality.

The number of rows returned by TOP() may be controlled using a variable:

SELECT TOP (@Variable)

For more details about using variables, turn to Chapter 16, “Programming with T-SQL.”

The WITH TIES Option

The WITH TIES option is important to the TOP() predicate. It enables the last place to

include multiple rows if those rows have equal values in the columns used in the ORDER BY

clause. The following version of the preceding query includes the WITH TIES option and

correctly results in fi ve rows from a TOP 6 predicate:

USE AdventureWorks;

SELECT TOP(6) WITH TIES

ProductNumber, Name, ListPrice,

CONVERT(VARCHAR(10),SellStartDate,1) SellStartDate

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

http://www.it-ebooks.info