SQL RANK Function

The RANK function returns a rank for each row.

Rankings can also be applied to groups (partitions) within the rows.

Ranking can have gaps, and equal values have equal ranks.

On the other hand, DENSE_RANK will not have gaps after equal values have received equal ranks.

Example

#

List products sorted by price. Rank each row.

SELECT ProductName, UnitPrice,
       RANK() OVER(ORDER BY UnitPrice) AS Rank
  FROM Product

Rankings can have gaps. Equal values have equal ranks.

Result:  78 records
ProductName UnitPrice Rank
Geitost 2.50 1
Guaraná Fantástica 4.50 2
Konbu 6.00 3
Filo Mix 7.00 4
Tourtière 7.45 5
Rhönbräu Klosterbier 7.75 6
Tunnbröd 9.00 7
Teatime Chocolate Biscuits 9.20 8
Zaanse koeken 9.50 9
Rogede sild 9.50 9
Jack's New England Clam Chowder 9.65 11
Stroopwafels 9.75 12
Longlife Tofu 10.00 13
Sir Rodney's Scones 10.00 13
Aniseed Syrup 10.00 13
Spegesild 12.00 16

Syntax

#

Syntax of the RANK function.

RANK() 
  OVER( PARTITION BY expression, ...
        ORDER BY expression, ... )

PARTITION BY expression, ... -- optional. Organizes rows into different groups where the RANK will be applied. If not specified, the entire result set represents a single group.

ORDER BY expression -- sets the sort order of the result set.


More Examples

RANK with PARTITION

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: List the orders for 2013 ranked by order amount within each month.
SELECT MONTH(OrderDate) AS Month,
       CONCAT(FirstName, ' ', LastName) AS Customer, TotalAmount, 
       RANK() OVER(PARTITION BY MONTH(OrderDate)
                        ORDER BY TotalAmount DESC) AS Rank
  FROM [Order] O
  JOIN Customer C ON O.CustomerId = C.Id
 WHERE YEAR(OrderDate) = 2013
Result:  408 records
Month Customer TotalAmount Rank
1 Jean Fresnière 11493.20 1
1 Jytte Petersen 11283.20 2
1 Roland Mendel 5796.00 3
1 Maurizio Moroni 192.00 31
1 Yoshi Latimer 102.40 32
1 Paolo Accorti 49.80 33
2, 3, 4, ... 11
12 Roland Mendel 6984.50 1
12 Marias Larsson 4337.00 2
12 Martín Sommer 4035.80 3
12 Pascale Cartrain 28.00 46
12 Paolo Accorti 18.40 47
12 Patricio Simpson 12.50 48

RANK with OTHER RANKING FUNCTIONS

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: List products with prices between $8 and $12. Include four common ranking functions based on price.
SELECT ProductName, UnitPrice,
       ROW_NUMBER() OVER(ORDER BY UnitPrice) AS RowNumber,
       RANK() OVER(ORDER BY UnitPrice) AS Rank,
       DENSE_RANK() OVER(ORDER BY UnitPrice) AS DenseRank,
       NTILE(4) OVER(ORDER BY UnitPrice) AS Quartile
  FROM Product
 WHERE UnitPrice BETWEEN 8 AND 12
 ORDER BY UnitPrice
Result:  10 records
ProductName UnitPrice RowNumber Rank DenseRank Quartile
Tunnbröd 9.00 1 1 1 1
Teatime Chocolate Biscuits 9.20 2 2 2 1
Rogede sild 9.50 3 3 3 1
Zaanse koeken 9.50 4 3 3 2
Jack's New England Clam Chowder 9.65 5 5 4 2
Stroopwafels 9.75 6 6 5 2
Aniseed Syrup 10.00 7 7 6 3
Sir Rodney's Scones 10.00 8 7 6 3
Longlife Tofu 10.00 9 7 6 4
Spegesild 12.00 10 10 7 4

You may also like



Guides


vsn 3.1