Ranking and Top-N per Group

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 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.

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.

त्वरित जाँच: Which function gives tied rows the same number and then skips numbers?

• RANK
• ROW_NUMBER
• DENSE_RANK
• NTILE