Learn SQL  /  Advanced Queries  /  Window Functions

Window Functions

Window functions compute a value across a set of related rows, like an aggregate — but unlike GROUP BY, they do not collapse the rows, so you keep every original row plus the computed value.

ROW_NUMBER() — Numbering Rows

R Code
SELECT name, score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS rank_num
FROM students;
Output
name  | score | rank_num
Meera | 92    | 1
Rafi  | 88    | 2
Zayn  | 76    | 3
Nadia | 65    | 4

PARTITION BY — Resetting the Window Per Group

This is the key idea that makes window functions so powerful: PARTITION BY restarts the calculation for each group, similar to GROUP BY, but WITHOUT collapsing the rows.

R Code
SELECT name, class, score,
  RANK() OVER (PARTITION BY class ORDER BY score DESC) AS rank_in_class
FROM students;
Output
name  | class | score | rank_in_class
Meera | A     | 92    | 1
Rafi  | A     | 88    | 2
Zayn  | B     | 76    | 1
Nadia | B     | 65    | 2
💡 Tip
This is exactly how you find the top-N students WITHIN EACH class, which plain LIMIT (mentioned on the LIMIT & OFFSET page) cannot do on its own - wrap this query as a subquery/CTE and filter WHERE rank_in_class <= N.

RANK() vs DENSE_RANK() vs ROW_NUMBER()

R Code
-- with a tie at score = 80 for two students:
SELECT name, score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
  RANK()       OVER (ORDER BY score DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk
FROM students;
Output
name    | score | row_num | rnk | dense_rnk
Meera   | 92    | 1       | 1   | 1
Student | 80    | 2       | 2   | 2
Student | 80    | 3       | 2   | 2
Zayn    | 76    | 4       | 4   | 3

ROW_NUMBER always gives unique, sequential numbers even on ties. RANK gives tied rows the same rank, then skips the next number (2, 2, 4). DENSE_RANK also ties rows equally, but does not skip (2, 2, 3).

Advanced: Running Total

🚀 Advanced Example
SUM() as a window function with ORDER BY (and no PARTITION BY) computes a running total, row by row.
R Code
SELECT name, score,
  SUM(score) OVER (ORDER BY id) AS running_total
FROM students;
Output
name  | score | running_total
Rafi  | 88    | 88
Meera | 92    | 180
Zayn  | 76    | 256
Nadia | 65    | 321

Key Points

  • Window functions compute a value across related rows WITHOUT collapsing them, unlike GROUP BY.
  • OVER (...) defines the 'window' of rows the function operates on.
  • PARTITION BY resets the calculation per group; ORDER BY inside OVER controls the row sequence.
  • ROW_NUMBER gives unique sequential numbers; RANK skips numbers after ties; DENSE_RANK does not skip.