Microsoft® SQL Server® 2012 Bible

138

Part II: Building Databases and Working with Data

Sport-100 Helmet, Red

Sport-100 Helmet, Red

Sport-100 Helmet, Red

Sport-100 Helmet, Red

Sport-100 Helmet, Red

With the DISTINCT predicate:

USE AdventureWorks;

SELECT DISTINCT p.Name

FROM Production.Product p

INNER JOIN Sales.SalesOrderDetail so

ON p.ProductID = so.ProductID;

Result:

Name

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

Sport-100 Helmet, Red

Sport-100 Helmet, Black

Mountain Bike Socks, M

Mountain Bike Socks, L

Sport-100 Helmet, Blue

AWC Logo Cap

Long-Sleeve Logo Jersey, S

Whereas the fi rst query returned 121,317 rows, the DISTINCT predicate in the second

query eliminated the duplicate rows and returned only 266 unique rows.

SQL Server’s DISTINCT is different from MS Access’ distinctrow, which eliminates duplicates based on data

in the source tables, not duplicates in the result set of the query.

Select DISTINCT functions as though a GROUP BY clause (see Chapter 10) exists on every

output column.

Of course, using DISTINCT is based on the query’s requirements, so there may be no

choice; just be aware that depending on the size and mix of the data, there may be a per-

formance impact.

TOP () ...............................................................................................................

By defi nition, SELECT works with sets of data. Sometimes, however, it’s only the fi rst few

rows from the set that are of interest. For these situations, SQL Server includes several ways

to fi lter the results and fi nd the top rows.

c06.indd 138c06.indd 138 7/30/2012 4:16:07 PM7/30/2012 4:16:07 PM

http://www.it-ebooks.info