SQL Ranking Functions
Ranking functions number rows within an ordered window. ROW_NUMBER gives each row a separate number, while RANK and DENSE_RANK preserve different meanings when values tie. Choose based on the report's rules.
Understand the idea
This query ranks product prices from highest to lowest. ROW_NUMBER uses product_id to make tied ordering deterministic. RANK uses price alone so equal prices receive the same rank. RANK leaves gaps after ties, while DENSE_RANK does not. The seed prices are all different, so add a tie to see the distinction.
Where to try it
Run this example in the SQL playground. The lesson link loads the shop tables and sample rows before this query. Select Run when you are ready. Every run starts fresh, so your changes do not affect the website or a previous run.
SQLite sample data · MySQL / MariaDB sample data · See the sample tables
A worked example
SELECT name, price_cents,
ROW_NUMBER() OVER (ORDER BY price_cents DESC, product_id) AS position,
RANK() OVER (ORDER BY price_cents DESC) AS price_rank
FROM products
ORDER BY position;
Read the result
Desk lamp is first, Canvas bag second, Pencil set third, and Pocket notebook fourth. Both ranking columns agree because there are no tied prices yet.
Try one change
Set Pencil set's price equal to Canvas bag's in a disposable run. Add DENSE_RANK and compare all three numbering rules.