SQL DENSE_RANK Function

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.

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 of the DENSE_RANK function.

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

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

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

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

• Our Sql Window Functions Reference
• Our Sql PERCENT_RANK Function Reference
• Our Sql RANK Function Reference