Window Functions

Introduction

GROUP BY is great for summarizing data, but it has a limitation: it collapses many rows into one row per group, so you lose access to the individual rows. Sometimes you want a calculation — like a rank or a running total — attached to every individual row, without losing any of them.

Window functions solve exactly this problem. This lesson covers what a window function is, the ROW_NUMBER(), RANK(), and DENSE_RANK() functions, and how PARTITION BY lets you reset a calculation per group.

What is a Window Function?

A window function performs a calculation across a set of related rows — called a "window" — while still returning one output row for every input row. Unlike an aggregate function used with GROUP BY, it does not collapse the rows together.

Window Functions vs GROUP BY

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    grade VARCHAR(10),
    marks INT
);

INSERT INTO students VALUES
    (1, 'Alex', '10th', 82),
    (2, 'Priya', '10th', 91),
    (3, 'Sam', '10th', 91),
    (4, 'Riya', '11th', 70),
    (5, 'Dev', '11th', 88);

-- GROUP BY collapses rows into one per grade
SELECT grade, AVG(marks) AS avg_marks
FROM students
GROUP BY grade;

+-------+------------+
| grade | avg_marks  |
+-------+------------+
| 10th  | 88.0000    |
| 11th  | 79.0000    |
+-------+------------+

Notice all five individual students disappeared — only two summary rows remain. A window function keeps every row visible while still calculating something in relation to its group.

ROW_NUMBER()

ROW_NUMBER() assigns a unique, sequential number to each row, based on the ordering you specify with OVER (ORDER BY ...). No two rows ever get the same number, even if their values are tied.

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

+-------+-------+---------+
| name  | marks | row_num |
+-------+-------+---------+
| Priya | 91    | 1       |
| Sam   | 91    | 2       |
| Dev   | 88    | 3       |
| Alex  | 82    | 4       |
| Riya  | 70    | 5       |
+-------+-------+---------+

Notice Priya and Sam are tied at 91 marks, but ROW_NUMBER() still gives them different numbers (1 and 2) — it always produces a strict, unique sequence.

RANK() and DENSE_RANK()

RANK() and DENSE_RANK() both handle ties by giving equal rows the same rank — the difference is what happens to the rank number right after a tie.

SELECT name, marks,
    RANK() OVER (ORDER BY marks DESC) AS rank_num,
    DENSE_RANK() OVER (ORDER BY marks DESC) AS dense_rank_num
FROM students;

+-------+-------+----------+----------------+
| name  | marks | rank_num | dense_rank_num |
+-------+-------+----------+----------------+
| Priya | 91    | 1        | 1              |
| Sam   | 91    | 1        | 1              |
| Dev   | 88    | 3        | 2              |
| Alex  | 82    | 4        | 3              |
| Riya  | 70    | 5        | 4              |
+-------+-------+----------+----------------+

RANK() skips a number after a tie (jumping straight from 1 to 3, since two rows tied for 1st), while DENSE_RANK() never skips (going 1, 1, 2, 3, 4 with no gaps).

PARTITION BY

By default, OVER (ORDER BY ...) treats the entire table as one window. PARTITION BY splits that window into groups, so the calculation restarts from scratch within each group — similar in spirit to GROUP BY, but without collapsing any rows.

SELECT name, grade, marks,
    RANK() OVER (PARTITION BY grade ORDER BY marks DESC) AS rank_in_grade
FROM students;

+-------+-------+-------+---------------+
| name  | grade | marks | rank_in_grade |
+-------+-------+-------+---------------+
| Priya | 10th  | 91    | 1             |
| Sam   | 10th  | 91    | 1             |
| Alex  | 10th  | 82    | 3             |
| Dev   | 11th  | 88    | 1             |
| Riya  | 11th  | 70    | 2             |
+-------+-------+-------+---------------+

Notice the ranking restarts at 1 for each grade group. Alex is ranked 4th overall in marks but 3rd within just the 10th grade group, and Dev is ranked 1st within 11th grade despite having lower marks than several 10th graders.

Worked Example: Ranking by Grade Group

Putting it all together, here is a complete query ranking students by marks within each grade, using DENSE_RANK() so tied students share a rank with no gaps afterward.

SELECT name, grade, marks,
    DENSE_RANK() OVER (
        PARTITION BY grade
        ORDER BY marks DESC
    ) AS grade_rank
FROM students
ORDER BY grade, grade_rank;

+-------+-------+-------+------------+
| name  | grade | marks | grade_rank |
+-------+-------+-------+------------+
| Priya | 10th  | 91    | 1          |
| Sam   | 10th  | 91    | 1          |
| Alex  | 10th  | 82    | 2          |
| Dev   | 11th  | 88    | 1          |
| Riya  | 11th  | 70    | 2          |
+-------+-------+-------+------------+

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

Window functions let you attach a calculation — like a rank or row number — to every individual row, based on a window of related rows, without losing any of those rows the way GROUP BY does.

In this lesson, you learned ROW_NUMBER(), RANK(), and DENSE_RANK(), how PARTITION BY resets a calculation per group, and used all of it together to rank students by marks within each grade. Next, you will learn about temporary tables.