Jerry Nixon @Work: SQL 2005 tSQL: rank(), row_number(), and dense_rank()

Jerry Nixon on Windows

Friday, March 14, 2008

SQL 2005 tSQL: rank(), row_number(), and dense_rank()

When I presented at PASS a few years back, I intended to post some of the fun things I talked about. I forgot. One gem was using rank() row_number, and dense_rank(). Each applies a logical number to result records. Here's my sample for Northwind (or Pubs, I can't recall) data:

Select 
Title
From
Employees

/*
rank()

Returns the rank of each row within the partition of a result set.
The rank of a row is one plus the number of ranks that come before the row in question.
*/

Select
Rank() OVER (ORDER BY Title) As [Rank]
,Title
From
Employees
GO

/*
dense_rank()

Returns the rank of rows within the partition of a result set, WITHOUT ANY GAPS in the ranking.
The rank of a row is one plus the number of distinct ranks that come before the row in question.
*/

Select
Dense_Rank() OVER (ORDER BY Title) As [DenseRank]
,Title
From
Employees
GO

/*
row_number()

Returns the sequential number of a row within a partition of a result set,
starting at 1 for the first row in each partition.
*/

Select
Row_Number() OVER (ORDER BY Title) As [RowNumber]
,Title
From
Employees
GO

/*
row_number (PARTION BY)

Divides the result set produced by the FROM clause into partitions
to which the ROW_NUMBER function is applied.
*/

Select
Row_Number() OVER (PARTITION BY Title ORDER BY Title) As [RowNumber]
,Title
From
Employees