Detailed explanation
ROW_NUMBER(), RANK(), and DENSE_RANK() are SQL window functions that assign numbers to rows according to an ORDER BY clause inside OVER(...). Unlike GROUP BY, they preserve individual rows rather than collapse them into aggregates. Their main difference is how they handle ties: ROW_NUMBER assigns a different consecutive number to every row, RANK assigns equal ranks to ties and leaves gaps afterward, and DENSE_RANK assigns equal ranks without gaps. These distinctions matter when selecting top performers, building leaderboards, or finding the nth-highest distinct salary.
For salaries 500, 500, and 400, ordered by salary DESC, ROW_NUMBER produces 1, 2, 3; RANK produces 1, 1, 3; and DENSE_RANK produces 1, 1, 2. RANK represents the position where a tied group begins: because two rows occupy the first two positions, the next salary receives rank 3. DENSE_RANK counts distinct ordering-value groups instead, so the next salary receives rank 2. Rows are tied only when all expressions in the window ORDER BY compare equally, not merely the first expression.
Typical syntax is RANK() OVER (PARTITION BY department_id ORDER BY salary DESC). PARTITION BY restarts numbering for each department; omitting it treats the result as one partition. Conceptually, the database processes rows in each partition's ordering, tracking row positions and changes in ordering values. Execution commonly requires sorting, although suitable indexes or existing input order may reduce that work. Window ordering controls the calculation, but it does not guarantee the final display order; use an outer ORDER BY for that.
Choose ROW_NUMBER for selecting exactly N rows per group, provided enough rows exist, or for deduplication with a clear selection rule. Add a unique tie-breaker, such as employee_id, when its assignment must be deterministic. Choose RANK for competition-style positions and DENSE_RANK for the top N distinct salary levels. Do not add employee_id to a salary-based RANK or DENSE_RANK ordering if equal salaries should remain tied. Interviewers often test whether you understand these output differences and can filter computed rankings using a CTE or subquery, since WHERE in the same query block cannot directly filter window-function results.
Key points to remember
- ROW_NUMBER gives every row a unique consecutive number within its partition.
- RANK gives ties the same rank and skips subsequent positions.
- DENSE_RANK gives ties the same rank without leaving gaps.
- PARTITION BY defines independent ranking groups; window ORDER BY defines positions and ties.
- ROW_NUMBER <= N limits rows per group; DENSE_RANK <= N selects the top N distinct ordering-value groups.
Common mistakes candidates make
- Assuming ROW_NUMBER assigns tied rows consistently without a unique ordering tie-breaker.
- Adding a unique ID to RANK or DENSE_RANK ordering and unintentionally breaking salary ties.
- Filtering a window-function result directly in the same query block's WHERE clause instead of using a CTE or subquery.
Likely follow-up questions
How do you find employees earning the second-highest distinct salary?
Compute DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank in a CTE, then filter WHERE salary_rank = 2 outside it. This returns every employee at that salary level; exclude NULL salaries first if NULL means unknown.
Which function should you use for the top three employees per department?
For at most three employees per department, use ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC, employee_id) and filter row numbers up to 3. If the requirement includes everyone tied at the third employee's salary, use RANK ordered only by salary and filter ranks up to 3.
When do all three functions return the same numbers?
They return the same numbers when the window ORDER BY values are unique within each partition. Without ties, every row starts a new rank and a new dense rank, so all three sequences are consecutive.
Interview tip: Start with tie handling, demonstrate the 500, 500, 400 example, and finish with one practical use case for each function.