Updated:
17 July 2006
Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.
Transact-SQL provides the following ranking functions:
The following shows the four ranking functions used in the same query. See each ranking function for function specific examples.
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS 'Row Number'
,RANK() OVER (ORDER BY a.PostalCode) AS 'Rank'
,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS 'Dense Rank'
,NTILE(4) OVER (ORDER BY a.PostalCode) AS 'Quartile'
,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
INNER JOIN Person.Contact c
ON s.SalesPersonID = c.ContactID
INNER JOIN Person.Address a
ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;
Here is the result set.
|
FirstName
|
LastName
|
Row Number
|
Rank
|
Dense Rank
|
Quartile
|
SalesYTD
|
PostalCode
|
|---|
|
Maciej
|
Dusza
|
1
|
1
|
1
|
1
|
4557045
|
98027
|
|
Shelley
|
Dyck
|
2
|
1
|
1
|
1
|
5200475
|
98027
|
|
Linda
|
Ecoffey
|
3
|
1
|
1
|
1
|
3857164
|
98027
|
|
Carla
|
Eldridge
|
4
|
1
|
1
|
1
|
1764939
|
98027
|
|
Carol
|
Elliott
|
5
|
1
|
1
|
2
|
2811013
|
98027
|
|
Jauna
|
Elson
|
6
|
6
|
2
|
2
|
3018725
|
98055
|
|
Michael
|
Emanuel
|
7
|
6
|
2
|
2
|
3189356
|
98055
|
|
Terry
|
Eminhizer
|
8
|
6
|
2
|
3
|
3587378
|
98055
|
|
Gail
|
Erickson
|
9
|
6
|
2
|
3
|
5015682
|
98055
|
|
Mark
|
Erickson
|
10
|
6
|
2
|
3
|
3827950
|
98055
|
|
Martha
|
Espinoza
|
11
|
6
|
2
|
4
|
1931620
|
98055
|
|
Janeth
|
Esteves
|
12
|
6
|
2
|
4
|
2241204
|
98055
|
|
Twanna
|
Evans
|
13
|
6
|
2
|
4
|
1758386
|
98055
|
Change History
|
Release
|
History
|
|---|
|
17 July 2006
|
-
New content:
-
-
Added example that shows using all four ranking functions.
|
Reference
Functions (Transact-SQL)
OVER Clause (Transact-SQL)
Help and Information
Getting SQL Server 2005 Assistance