Advanced · Lesson 12 of 14

ROW_NUMBER, RANK, DENSE_RANK

Rank and pick top-N per group.

Three ways to rank

ROW_NUMBER always gives distinct positions. RANK leaves gaps after ties. DENSE_RANK does not. Which one you want depends on whether ties should borrow a slot.

Top-N per group

Wrapping ROW_NUMBER() OVER (PARTITION BY group ORDER BY measure DESC) and keeping rn <= N is the one pattern to know cold.

Tie-breakers

Add a second ordering key such as hire_date to make the ranking deterministic. Interviewers specifically hunt for this.

Examples

Compare the three

Salaries are distinct here, so the three agree.

SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn, RANK() OVER (ORDER BY salary DESC) AS rk, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr FROM employees;

Top earners per category

Numbering resets per department.

SELECT name, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn FROM employees;