259
Chapter 10: Aggregating, Windowing, and Ranking Data
10
Ranking Functions .............................................................................................
The windowing capability (the OVER() clause) by itself doesn’t create any query output
columns; that’s where the ranking functions come into play:■ (^) row_number
■ (^) rank
■ (^) dense_rank
■ (^) ntile
Just to be explicit, the ranking functions all require the windowing function. These func-
tions add a rank to each row of the subset of data. The ranking of the rows depends on
which function that is specifi ed.
You can use all the normal aggregate functions — SUM(), MIN(), MAX(), COUNT(*), and
so on — as ranking functions.
Row number() Function
The ROW_NUMBER() function generates an on-the-fl y auto-incrementing integer according
to the sort order of the OVER() clause. It’s similar to Oracle’s RowNum column.
The row number function simply numbers the rows in the query result; there’s absolutely
no correlation with any physical address or absolute row number. This is important because
in a relational database, row position, number, and order have no meaning. It also means
that as rows are added or deleted from the underlying data source, the row numbers for the
query results will change. In addition, if there are sets of rows with the same values in all
ordering columns, then their order is undefi ned, so their row numbers may change between
two executions even if the underlying data does not change.
One common practical use of the ROW_NUMBER() function is to fi lter by the row number
values for pagination. For example, a query that easily produces rows 21–40 would be use-
ful for returning the second page of data for a web page. Just be aware that the rows in the
pages may change — typically, this grabs data from a temp table.
It would seem that the natural way to build a row number pagination query would be to
simply add the OVER() clause and ROW_NUMBER() function to the WHERE clause:
USE AdventureWorks
GO
SELECT
ROW_NUMBER() OVER(ORDER BY PurchaseOrderID, ShipDate)
AS RowNumber,
PurchaseOrderID,
ShipDate
FROM Purchasing.PurchaseOrderHeader
c10.indd 259c10.indd 259 7/30/2012 4:26:13 PM7/30/2012 4:26:13 PM
http://www.it-ebooks.info