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