Window Functions — ROW_NUMBER, RANK, and OVER

Window Functions

A window function performs a calculation across a set of rows that are related to the current row — its *window* — without collapsing them into a single output row the way GROUP BY does. Every result row retains its individual identity while also receiving the computed value.

The OVER clause defines the window. Inside OVER you can optionally specify PARTITION BY (to divide rows into independent groups) and ORDER BY (to determine the order within each partition).

ROW_NUMBER, RANK, and DENSE_RANK

• ROW_NUMBER() — assigns a unique sequential integer starting at 1; no ties
• RANK() — same rank for tied rows, then skips the next rank(s)
• DENSE_RANK() — same rank for tied rows, but the next rank is always consecutive

-- Rank students by score within each course
SELECT
  student_id,
  course_id,
  score,
  ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY score DESC) AS row_num,
  RANK()       OVER (PARTITION BY course_id ORDER BY score DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY course_id ORDER BY score DESC) AS dense_rnk
FROM enrolments
WHERE score IS NOT NULL;

A common use-case is fetching the top-N rows per group — for example, the top-3 scorers in every course. You wrap the window function in a subquery or CTE and then filter on the rank.

-- Top 3 scorers per course
SELECT student_id, course_id, score, rnk
FROM (
  SELECT
    student_id,
    course_id,
    score,
    RANK() OVER (PARTITION BY course_id ORDER BY score DESC) AS rnk
  FROM enrolments
  WHERE score IS NOT NULL
) ranked
WHERE rnk <= 3
ORDER BY course_id, rnk;