# Ranking and Top-N per Group — SQL Deep Dive

Source: https://www.skillbyai.com/en/sql-deep-dive/w-rank

> ROW_NUMBER, RANK, DENSE_RANK.

## OVER (PARTITION BY ... ORDER BY ...)

A window function adds a value to each row computed over a **window** of related rows: `PARTITION BY` splits rows into groups and `ORDER BY` orders them inside each group. `ROW_NUMBER` gives unique numbers (add a tiebreaker for determinism), `RANK` gives ties the same rank and skips numbers, and `DENSE_RANK` does not skip. For **top-N per group**, compute ROW_NUMBER in a subquery and filter `rn <= N` outside, because window functions cannot appear in WHERE.

## Aggregates that keep the rows

Window functions compute across related rows without collapsing them, enabling rankings, running totals and comparisons.

![Three ideas: ranking, running and moving calculations, shares and bands.](assets/figures/sql-deep-dive/section-4-map.svg) — Figure 4.1 — Ranking, running totals and shares.

## Three ranking functions and the top earner per department, run

I ran this with psql on PostgreSQL 16.2 against the course sample data (shown in the first section). Ravi and Meera tie: ROW_NUMBER separates them using id as a tiebreaker, RANK gives both 2 and skips 3, DENSE_RANK gives John 3.

```sql
SELECT name, dept_id, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC, id) AS row_number,
       RANK()       OVER w AS rank,
       DENSE_RANK() OVER w AS dense_rank
FROM employees WHERE dept_id = 1
WINDOW w AS (ORDER BY salary DESC)
ORDER BY row_number;

-- Top earner per department
SELECT dept_id, name, salary FROM (
  SELECT e.*, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, id) AS rn
  FROM employees e WHERE dept_id IS NOT NULL
) ranked WHERE rn = 1 ORDER BY dept_id;
```

Output:

```
 name  | dept_id | salary | row_number | rank | dense_rank 
-------+---------+--------+------------+------+------------
 Asha  |       1 | 250000 |          1 |    1 |          1
 Ravi  |       1 | 180000 |          2 |    2 |          2
 Meera |       1 | 180000 |          3 |    2 |          2
 John  |       1 | 120000 |          4 |    4 |          3
(4 rows)

 dept_id | name | salary 
---------+------+--------
       1 | Asha | 250000
       2 | Sara | 150000
       3 | Omar |  70000
(3 rows)
```

## Choose the function by the tie rule

Ask "should ties share a place?" before choosing ROW_NUMBER, RANK or DENSE_RANK.

**Quiz:** Which function gives tied rows the same number and then skips numbers?

- [x] RANK
- [ ] ROW_NUMBER
- [ ] DENSE_RANK
- [ ] NTILE

*Answer:* RANK. 1, 2, 2, 4.