# SQL ROW_NUMBER Function

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
``````
Result:  78 records
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.

## ROW_NUMBER with PARTITION

Problem: List the orders for 2013 ranked by order amount within each month.
``````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
``````
Result:  408 records
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 Martín Sommer 4035.80 3
12 Pascale Cartrain 28.00 46
12 Paolo Accorti 18.40 47
12 Patricio Simpson 12.50 48

## ROW_NUMBER with OTHER RANKING FUNCTIONS

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

Guides

vsn 3.1