Window Functions: ROW_NUMBER, RANK, and More

Window functions are one of SQL's most powerful features — they let you run a calculation across a set of related rows, without collapsing those rows into a single output row the way GROUP BY does.

The key idea: OVER()

Any window function is followed by OVER (...), which defines its "window" — the set of rows it should consider for each row of output.

ROW_NUMBER() — numbering rows

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

Unlike GROUP BY, every original row is still present — we've just added a running number alongside it.

PARTITION BY — resetting the window per group

This is the real superpower — combine ranking within each group, in a single query, something GROUP BY alone cannot do:

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

This numbers employees 1, 2, 3... within each department separately — the numbering restarts for every new department_id.

Finding the top N per group

A hugely common real-world pattern — "highest paid employee in each department":

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

RANK() and DENSE_RANK()

Unlike ROW_NUMBER(), these give tied rows the same rank:

SELECT first_name, salary,
  RANK() OVER (ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;

RANK() leaves a gap after ties (1, 2, 2, 4); DENSE_RANK() doesn't (1, 2, 2, 3).

Running totals with SUM() OVER()

SELECT order_date, id,
  SUM(id) OVER (ORDER BY order_date) AS running_total
FROM orders;

LAG() and LEAD() — looking at neighboring rows

Access the previous or next row's value without a self-join:

SELECT first_name, salary,
  LAG(salary) OVER (ORDER BY salary) AS next_lowest_salary,
  LEAD(salary) OVER (ORDER BY salary) AS next_highest_salary
FROM employees;

Engine support note

Window functions are supported in PostgreSQL, SQL Server, MySQL 8+, and SQLite 3.25+ — essentially everywhere modern. Older MySQL (5.x) does not support them.

Open the Code Playground, pick any SQL engine (SQLite, PostgreSQL, MySQL, or SQL Server), and paste these queries in directly — the practice database above is already loaded for you, every time.

-- A ready-to-query practice database is loaded automatically before every run —
-- no CREATE TABLE needed. Available tables:
--   departments  (id, name, location)
--   employees    (id, first_name, last_name, email, department_id, job_title, salary, hire_date, manager_id)
--   customers    (id, customer_name, city, country, signup_date)
--   products     (id, product_name, category, price, stock_quantity)
--   orders       (id, customer_id, employee_id, order_date, status)
--   order_items  (id, order_id, product_id, quantity, unit_price)

SELECT * FROM employees WHERE department_id = 1;