SQL Window Functions — Analyze Rows Without Grouping Them: – RANK, ROW_NUMBER, LEAD, LAG

A prize list needs each student’s mark and rank. Grouping to one class summary would lose the individual rows.

A window function computes a value using a related set of rows while retaining individual result rows. It can rank, compare neighbors or calculate running totals.

OVER describes partitions and window ordering. RANK gives equal order values equal ranks and leaves gaps; DENSE_RANK does not leave those gaps; ROW_NUMBER assigns a separate sequence number. A running aggregate also depends on its frame, which controls which ordered rows contribute.

Related concepts: Sort Results and Group and Summarize and Common Table Expressions.

Window functions perform calculations across a “window” of rows related to the current row. Unlike GROUP BY, they don’t collapse results. Instead, they add new analytical columns — such as ranks, moving averages, or differences — while preserving all rows.

SQL · portable concepts; preserved examples identify their engine

function_name(...) OVER (
  [PARTITION BY ...]
  [ORDER BY ...]
)

SQL · portable concepts; preserved examples identify their engine

CREATE TABLE marks (
  roll_no INT,
  name VARCHAR(50),
  subject VARCHAR(30),
  marks INT
);

INSERT INTO marks VALUES
(1, 'Aarav', 'Maths', 95),
(2, 'Diya', 'Maths', 85),
(3, 'Sneha', 'Maths', 92),
(4, 'Karan', 'Maths', 78),
(1, 'Aarav', 'Science', 90),
(2, 'Diya', 'Science', 80),
(3, 'Sneha', 'Science', 92),
(4, 'Karan', 'Science', 88);

SQL · portable concepts; preserved examples identify their engine

SELECT name, subject, marks,
  RANK() OVER (PARTITION BY subject ORDER BY marks DESC) AS rank
FROM marks;

name   | subject | marks | rank
-------+---------+-------+------
Aarav  | Maths   | 95    | 1
Sneha  | Maths   | 92    | 2
Diya   | Maths   | 85    | 3
Karan  | Maths   | 78    | 4
Sneha  | Science | 92    | 1
Aarav  | Science | 90    | 2
Karan  | Science | 88    | 3
Diya   | Science | 80    | 4

SQL · portable concepts; preserved examples identify their engine

SELECT name, subject, marks,
  ROW_NUMBER() OVER (PARTITION BY subject ORDER BY marks DESC) AS row_num
FROM marks;

Unlike RANK(), this never gives duplicate numbers.

SQL · portable concepts; preserved examples identify their engine

SELECT name, subject, marks,
  DENSE_RANK() OVER (PARTITION BY subject ORDER BY marks DESC) AS dense_rank
FROM marks;

Perfect for scoreboards where tied ranks shouldn't leave gaps.

SQL · portable concepts; preserved examples identify their engine

SELECT name, subject, marks,
  LEAD(marks) OVER (PARTITION BY subject ORDER BY marks DESC) AS next_marks,
  LAG(marks) OVER (PARTITION BY subject ORDER BY marks DESC) AS prev_marks
FROM marks;

name   | subject | marks | next_marks | prev_marks
--------+---------+-------+------------+-------------
Aarav  | Maths   | 95    | 92         | NULL
Sneha  | Maths   | 92    | 85         | 95
Diya   | Maths   | 85    | 78         | 92
Karan  | Maths   | 78    | NULL       | 85

Helps in comparisons like "How did this student score compared to others before/after?"

SQL · portable concepts; preserved examples identify their engine

SELECT name, subject, marks,
  SUM(marks) OVER (PARTITION BY subject ORDER BY marks DESC) AS cumulative_marks
FROM marks;

• Ranking students within a class or subject
• Showing performance trend using LAG() and LEAD()
• Cumulative scores per student across subjects
• Generate roll call with ROW_NUMBER()

• Always define an ORDER BY clause in the window
• Use PARTITION BY to reset calculations per group
• Be cautious with NULLs — LAG and LEAD may return them
• Avoid mixing GROUP BY and window functions in the same SELECT unless necessary

A window ORDER BY does not necessarily sort the final result. Add an outer ORDER BY. For deterministic ROW_NUMBER, include a tie-breaker; for a ranking that should preserve mark ties, do not accidentally add a unique key to the rank’s ordering. Specify ROWS frames when a row-by-row running total is intended.

Rank by marks while preserving the tie at 85, and sort the display by rank and roll number.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

SELECT name, marks, RANK() OVER (ORDER BY marks DESC) AS mark_rank FROM practice_students ORDER BY mark_rank, roll_no;

The two highest marks tie at rank one. RANK skips rank two, so the remaining student has rank three. The outer order settles the display order of the tied students.

A window function computes a value using a related set of rows while retaining individual result rows. It can rank, compare neighbors or calculate running totals.

The two highest marks tie at rank one. RANK skips rank two, so the remaining student has rank three. The outer order settles the display order of the tied students.

Continue your understanding with Sort Results and Group and Summarize and Common Table Expressions.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.