Dofactory.com
Dofactory.com
Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest freelancing marketplace for people like you.
By adding your name & email you agree to our terms, privacy and cookie policies.

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.

Example

#

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

#

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.


More Examples

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

ROW_NUMBER 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



Last updated on Dec 21, 2023

Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest freelancing marketplace for people like you.
By adding your name & email you agree to our terms, privacy and cookie policies.