SQL DENSE_RANK Function

The DENSE_RANK function returns a rank for each row.

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

Dense ranking have no gaps (i.e. are dense), and equal values have equal ranks.

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

Example

#

List products sorted by price. Rank each row.

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

Dense rankings can have gaps (i.e. are dense). 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 10
Stroopwafels 9.75 11
Longlife Tofu 10.00 12
Sir Rodney's Scones 10.00 12
Aniseed Syrup 10.00 12

Syntax

#

Syntax of the DENSE_RANK function.

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

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

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

More Examples

DENSE_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, 
       DENSE_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

DENSE_RANK with OTHER RANKING FUNCTIONS

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: List products with prices between $8 and $12. Include some 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