SQL to number and rank rows
Add a sequential number or rank to rows with window functions.
SELECT name,
score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
RANK() OVER (ORDER BY score DESC) AS rank,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM players;How to use ROW_NUMBER, RANK and DENSE_RANK
ROW_NUMBER, RANK, and DENSE_RANK all number rows in an order you choose — they differ only in how they treat ties.
How it works
- ROW_NUMBER always gives unique numbers 1, 2, 3… even when scores tie.
- RANK gives tied rows the same rank, then skips numbers (1, 1, 3).
- DENSE_RANK gives tied rows the same rank without skipping (1, 1, 2).
- Add PARTITION BY to restart the numbering within each group (e.g. per department).
Related SQL queries
- SQL to find the second highest value
- SQL to find the nth highest value
- SQL to get the top n rows per group
- SQL to find duplicate rows
- SQL to delete duplicate rows (keep one)
- SQL to count rows per group
Frequently asked questions
How do I use ROW_NUMBER, RANK and DENSE_RANK in SQL?
ROW_NUMBER, RANK, and DENSE_RANK all number rows in an order you choose — they differ only in how they treat ties. ROW_NUMBER always gives unique numbers 1, 2, 3… even when scores tie. RANK gives tied rows the same rank, then skips numbers (1, 1, 3). DENSE_RANK gives tied rows the same rank without skipping (1, 1, 2). Add PARTITION BY to restart the numbering within each group (e.g. per department).
Does this work in PostgreSQL, MySQL, SQL Server, and SQLite?
Yes — this query uses standard SQL that runs unchanged on PostgreSQL, MySQL, SQL Server, and SQLite.
Can I generate this query for my own tables?
Yes. Describe what you want in plain English with Forgly's free AI SQL Generator and it writes the query for your dialect.