The ROW_NUMBER
function returns a rank for each row.
Rankings can also be applied to groups (partitions) within the rows.
Row numbers are sequentila, have no gaps, and equal values have different ranks.
List products sorted by price. Rank each row with a sequential number.
SELECT ProductName, UnitPrice,
ROW_NUMBER() OVER(ORDER BY UnitPrice) AS 'Row Number'
FROM Product
ORDER BY UnitPrice
ProductName | UnitPrice | Row Number |
---|---|---|
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 | 10 |
Jack's New England Clam Chowder | 9.65 | 11 |
Stroopwafels | 9.75 | 12 |
Longlife Tofu | 10.00 | 13 |
Sir Rodney's Scones | 10.00 | 14 |
Aniseed Syrup | 10.00 | 15 |
![]() |
Syntax of the ROW_NUMBER function.
ROW_NUMBER() OVER( PARTITION BY expression, ... ORDER BY expression, ... )
PARTITION BY expression
-- optional. Organizes rows into different groups where the ROW_NUMBER
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.
ORDER |
---|
Id |
OrderDate |
OrderNumber |
CustomerId |
TotalAmount |
CUSTOMER |
---|
Id |
FirstName |
LastName |
City |
Country |
Phone |
SELECT MONTH(OrderDate) AS Month,
CONCAT(FirstName, ' ', LastName) AS Customer, TotalAmount,
ROW_NUMBER() OVER(PARTITION BY MONTH(OrderDate)
ORDER BY TotalAmount DESC) AS 'Row Number'
FROM [Order] O
JOIN Customer C ON O.CustomerId = C.Id
WHERE YEAR(OrderDate) = 2013
Month | Customer | TotalAmount | Row Number |
---|---|---|---|
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 |
PRODUCT |
---|
Id |
ProductName |
SupplierId |
UnitPrice |
Package |
IsDiscontinued |
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
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 |