SQL window functions — ROW_NUMBER, RANK, DENSE_RANK with real examples

Window functions compute a value across a set of rows that are "related" to the current row, without collapsing them like GROUP BY does.

The three ranking functions

SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rk,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dr
FROM employees;

If three employees in Engineering earn ₹150k, ₹150k, ₹120k:

name	dept	salary	rn	rk	dr
Alice	Eng	150,000	1	1	1
Bob	Eng	150,000	2	1	1
Carol	Eng	120,000	3	3	2

• ROW_NUMBER — always unique, ties broken arbitrarily.
• RANK — ties get the same rank, next rank is skipped (1, 1, 3).
• DENSE_RANK — ties get the same rank, no gaps (1, 1, 2).

Practical use — top N per group

-- Top 3 highest-paid per department
SELECT * FROM (
  SELECT name, department, salary,
         DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dr
  FROM employees
) t WHERE dr <= 3;

Running totals

SELECT
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions
ORDER BY date;

date	amount	running_total
2026-05-01	100	100
2026-05-02	50	150
2026-05-03	200	350

Lag / Lead — compare a row to its neighbors

-- How much did revenue change from the previous day?
SELECT
  date,
  revenue,
  LAG(revenue) OVER (ORDER BY date) AS yesterday,
  revenue - LAG(revenue) OVER (ORDER BY date) AS delta
FROM daily_revenue;

Why use these over GROUP BY?

GROUP BY collapses N rows into 1. Window functions preserve every row while attaching an aggregated value. So you can return per-employee details + their department rank in one query, no self-join needed.

Common interview question — second-highest salary

-- Old way (clunky)
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

-- Modern way
SELECT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr FROM employees
) t WHERE dr = 2 LIMIT 1;

Where window functions are supported

PostgreSQL ✅, MySQL 8+ ✅, SQL Server ✅, Oracle ✅, SQLite 3.25+ ✅. If you're stuck on MySQL 5.7, you must fall back to self-joins or variables.