ROW_NUMBER and RANK — ranking within groups

Window functions compute a value for each row based on a set of related rows (a "window") without collapsing the result. The OVER() clause defines the window.

OVER() — the window clause

function() OVER (
  PARTITION BY col   -- divide rows into groups
  ORDER BY    col    -- sort within each group
)

ROW_NUMBER() — unique sequential integers

Assigns 1, 2, 3, … with no ties. Ties get different numbers (arbitrary order):

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

RANK() — ties get the same number, next rank skips

Employees tied at salary share a rank; the next rank jumps:

salary  RANK
12000   1
12000   1
9000    3   ← skipped 2

DENSE_RANK() — ties share rank, no gaps

salary  DENSE_RANK
12000   1
12000   1
9000    2   ← no gap

Choosing between them

Filtering to top-N per group

Window functions cannot appear in WHERE. Wrap in a CTE or subquery:

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT * FROM ranked WHERE rn = 1;

Tips

• PARTITION BY is optional. Omitting it treats the whole result set as one partition.
• Multiple window functions in the same query share OVER() — each is independent.