Ranking functions are window functions that give each row a number based on an order. The three main ones only differ in how they handle ties:
| Function | With ties | Example with two rows tied in 2nd place |
|---|---|---|
ROW_NUMBER() |
Different numbers, never repeated | 1, 2, 3, 4 |
RANK() |
Same number, then skips | 1, 2, 2, 4 |
DENSE_RANK() |
Same number, no gaps | 1, 2, 2, 3 |
ROW_NUMBER() OVER ([PARTITION BY columns] ORDER BY columns)
In all three, the ORDER BY inside OVER is mandatory: without an order there is no ranking.
All three together
We sort the orders by date, newest first. Both Echo DOTs were ordered on the same day, September 1, so they are tied:
| product | orderDate | rowNumber | ranking | denseRanking |
|---|---|---|---|---|
| Balay Oven | 2022-09-10 | 1 | 1 | 1 |
| Nintendo Switch | 2022-09-05 | 2 | 2 | 2 |
| Echo DOT 3 | 2022-09-01 | 3 | 3 | 3 |
| Echo DOT 4 | 2022-09-01 | 4 | 3 | 3 |
| Play Station 5 | 2022-07-18 | 5 | 5 | 4 |
| Xiaomi Mi 11 | 2022-07-09 | 6 | 6 | 5 |
| MacBook Pro M1 | 2022-06-20 | 7 | 7 | 6 |
| Xbox series X | 2022-01-21 | 8 | 8 | 7 |
The PlayStation row shows the difference: RANK jumps from 3 to 5 because two orders share 3rd place, while DENSE_RANK carries on with 4.
ROW_NUMBER assigns numbers among the tied rows arbitrarily, and two runs may not match. That’s why the example adds orderId to the ORDER BY: the result is then always the same.Ranking within each group
With PARTITION BY the numbering restarts in each group. Each customer’s most expensive order gets number 1:
| name | product | totalPrice | position |
|---|---|---|---|
| Helen | Echo DOT 4 | 2999.50 | 1 |
| Helen | Echo DOT 3 | 1999.50 | 2 |
| Janice | MacBook Pro M1 | 2449.50 | 1 |
| Kimberly | Nintendo Switch | 899.97 | 1 |
| Mavin | Play Station 5 | 2199.80 | 1 |
| Mavin | Xbox series X | 999.98 | 2 |
| Peter | Xiaomi Mi 11 | 4304.25 | 1 |
Top N per group
This is one of the most typical SQL interview questions: “get each customer’s most expensive order” or “the three best-selling products in each category”. Since a window function can’t be used in WHERE, you compute it in a CTE and filter outside:
| name | product | totalPrice |
|---|---|---|
| Peter | Xiaomi Mi 11 | 4304.25 |
| Helen | Echo DOT 4 | 2999.50 |
| Janice | MacBook Pro M1 | 2449.50 |
| Mavin | Play Station 5 | 2199.80 |
| Kimberly | Nintendo Switch | 899.97 |
Change position = 1 to position <= 2 to get each customer’s two most expensive orders.
ROW_NUMBER or RANK here? It depends on what you want to do with ties. ROW_NUMBER returns exactly one order per customer even if two have the same amount; RANK returns both.
Removing duplicates with ROW_NUMBER
Another very common use: keeping a single row from each group of duplicates. Here, one order per product description, the most recent one:
| productDescription | product | orderDate |
|---|---|---|
| Alexa Speaker | Echo DOT 4 | 2022-09-01 |
| Apple Notebook | MacBook Pro M1 | 2022-06-20 |
| Appliance | Balay Oven | 2022-09-10 |
| Nintendo Game Console | Nintendo Switch | 2022-09-05 |
| Smartphone | Xiaomi Mi 11 | 2022-07-09 |
| Sony Game Console | Play Station 5 | 2022-07-18 |
| Xbox Game Console | Xbox series X | 2022-01-21 |
There are eight orders but seven descriptions: only one of the two Alexa speakers remains. In SQL Server the same CTE can be used to delete the duplicates: DELETE FROM numbered WHERE n > 1.
NTILE: splitting into equal groups
NTILE(n) splits the sorted rows into n groups of the same size (or nearly). It’s used for quartiles, deciles or bands:
| product | totalPrice | quartile |
|---|---|---|
| Xiaomi Mi 11 | 4304.25 | 1 |
| Echo DOT 4 | 2999.50 | 1 |
| MacBook Pro M1 | 2449.50 | 2 |
| Play Station 5 | 2199.80 | 2 |
| Echo DOT 3 | 1999.50 | 3 |
| Balay Oven | 1399.10 | 3 |
| Xbox series X | 999.98 | 4 |
| Nintendo Switch | 899.97 | 4 |
Practice
- Number the customers in alphabetical order of last name.
- Get each customer’s oldest order.
- Replace
ROW_NUMBERwithRANKin the top N example. Does anything change with this data? Why?
