Home / SQL / Reference / Window functions

SQL ROW_NUMBER, RANK, DENSE_RANK

Number or rank rows within groups.

Syntax

ROW_NUMBER() OVER (PARTITION BY … ORDER BY …)
RANK() OVER (…)
DENSE_RANK() OVER (…)

ROW_NUMBER gives 1, 2, 3 with no ties; RANK leaves gaps after ties (1, 2, 2, 4); DENSE_RANK does not (1, 2, 2, 3). Filter on them in an outer query or CTE.

Example

Query (on the shop database used in the SQL course)

SELECT category, name, price,
       ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
FROM products
ORDER BY category, rn;

Result

categorynamepricern
BooksDeep Learning (book)89.01
BooksPython Crash Course (book)45.02
BooksSQL Pocket Guide25.03
Electronics27-inch monitor329.01
ElectronicsNoise-cancelling headphones249.02
ElectronicsMechanical keyboard119.03
ElectronicsUSB-C hub49.04
ElectronicsWireless mouse29.05
FurnitureStanding desk549.01
FurnitureErgonomic chair399.02
FurnitureDesk lamp59.03
GroceryCoffee beans 1 kg32.01
GroceryGreen tea, 100 bags12.02
StationeryGel pens, pack of 1011.01
StationeryA5 notebook8.52

15 rows

Related

Learn it step by step in the free lesson CTEs & Window Functions, with exercises that check your queries.