HR wants each department's employees ranked by salary, 1st highest, 2nd highest, and so on, with every single employee getting a distinct number, even if two people happen to earn exactly the same amount. ROW_NUMBER() is built for exactly that.
Learning Objectives
- Define ROW_NUMBER() precisely, and state what makes it different from RANK() and DENSE_RANK() (next lesson).
- Write ROW_NUMBER() with PARTITION BY and ORDER BY correctly.
- Use ROW_NUMBER() to find the Nth-highest value within each group.
Partition
Each department, separately
ORDER BY
salary DESC, within each partition
ROW_NUMBER()
1, 2, 3... no ties, ever
Unique Rank Per Row
Even if salaries tie
What ROW_NUMBER() Actually Does
ROW_NUMBER() assigns a unique, sequential integer (1, 2, 3, ...) to every row within its partition, based on the ORDER BY inside OVER(). Two rows with the exact same salary still get two different numbers, one of them arbitrarily comes first, there's no tie-handling at all, that's specifically what the next lesson's RANK() and DENSE_RANK() are for.
Ranking Employees by Salary, Per Department
Every employee gets a salary_rank starting fresh at 1 within their own department, PARTITION BY department_id resets the numbering for each department, ORDER BY salary DESC decides who gets 1 (the highest earner in that department).
SELECT first_name, last_name, department_id, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;Finding the Second-Highest Salary Per Department
ROW_NUMBER() can't be filtered on directly in the same SELECT's WHERE clause, WHERE runs before window functions are computed. Wrapping the query in a subquery (or a CTE, covered in the next module) and filtering there is the actual fix:
SELECT * FROM (
SELECT first_name, last_name, department_id, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees
) ranked
WHERE salary_rank = 2;Try It Yourself
Write a query returning only the single highest-paid employee in each department, on MySQL.
SELECT * FROM (
SELECT first_name, last_name, department_id, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees
) ranked
WHERE salary_rank = 1;Frequently Asked Questions
Which row gets rank 1 when two employees have the exact same salary?
Whichever one ROW_NUMBER() happens to process first, which is not guaranteed to be consistent across runs unless the ORDER BY fully disambiguates ties (for example, ORDER BY salary DESC, id ASC as a tiebreaker). Without a tiebreaker, treat the choice between exactly-tied rows as arbitrary.
Why can't ROW_NUMBER() be filtered on directly in the same query's WHERE clause?
SQL evaluates WHERE before window functions, so salary_rank doesn't exist yet at the point WHERE would need to check it, every database in this course rejects WHERE salary_rank = 2 written directly in the same SELECT. Wrapping the window function in a subquery or CTE and filtering in an outer WHERE, which runs after the window function has already been computed, is the standard fix.
Common Beginner Mistakes
Writing WHERE salary_rank = 2 directly, without a subquery
This fails on every database here with some form of "column does not exist" or "invalid use of window function" error, WHERE can't see a window function's result in the same query level it was computed at. Wrap the window function in a subquery or CTE first, then filter in the outer query.
Forgetting ORDER BY inside OVER() when ranking is actually needed
ROW_NUMBER() OVER (PARTITION BY department_id) with no ORDER BY is legal on most of these databases but the numbering becomes arbitrary and unreliable, there's nothing telling it which row should be 1. Always include an ORDER BY inside OVER() whenever the actual rank, not just a unique number, matters.
Best Practices
- Add a tiebreaker column to ORDER BY (like id) whenever two rows could legitimately tie and the exact tie-breaking order matters.
- Wrap a window function in a subquery or CTE immediately whenever the result needs to be filtered, don't try to filter it in the same SELECT.
- Reach for ROW_NUMBER() specifically when every row needs a genuinely unique position, use RANK() or DENSE_RANK() instead when ties should share a rank.
Interview Questions
Write a query to find the second-highest salary in each department.
SELECT * FROM (SELECT first_name, last_name, department_id, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank FROM employees) ranked WHERE salary_rank = 2; — the inner query ranks every employee within their own department by salary descending, and the outer WHERE, which runs after the ranking is computed, keeps only rank 2 from each department.
Why does ROW_NUMBER() never produce a tie, even when two rows have identical ORDER BY values?
ROW_NUMBER() is defined to always produce a strictly increasing sequence (1, 2, 3, ...) within its partition, by design, with no tie-handling logic at all, unlike RANK() or DENSE_RANK(). When two rows are otherwise equal under the ORDER BY, the database still has to pick one to come first, arbitrarily, unless an explicit tiebreaker column is added to ORDER BY.
Summary
ROW_NUMBER() assigns a unique, sequential number to every row within its partition, with no tie-handling at all, two equal values still get two different numbers. Filtering its result requires a subquery or CTE, since WHERE can't see a window function's output in the same query level. Finding the Nth-highest value per group is its single most common real use.
What's Next?
SQL RANK() and DENSE_RANK() are next: two more ranking functions, both of which, unlike ROW_NUMBER(), actually let tied rows share a rank.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.
Or try 50 SQL practice questions →