Explain analytic (window) functions in Oracle SQL, like ROW_NUMBER and RANK

How it works

Analytic functions compute a value across a 'window' of related rows — defined by PARTITION BY and, optionally, an ORDER BY within each partition — without collapsing the result set the way GROUP BY does; every input row stays in the output, each with its own computed value. ROW_NUMBER assigns a unique sequential number per partition; RANK and DENSE_RANK do the same but handle ties differently (RANK leaves gaps after a tie, DENSE_RANK doesn't); SUM/AVG OVER compute running or partition-wide totals alongside the detail rows.

SELECT
  department_id,
  last_name,
  salary,
  RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank,
  SUM(salary) OVER (PARTITION BY department_id) AS dept_total_salary
FROM employees;

Related Questions