MySQL PARTITION BY Clause

The PARTITION BY clause in MySQL groups rows into separate partitions (windows) for use with window functions like RANK(), DENSE_RANK(), LEAD(), and LAG(). It must always be used inside an OVER() clause. If omitted, the entire table is treated as a single partition.

Syntax

window_function(expression)
OVER (PARTITION BY column_name [ORDER BY column_name] [frame_clause])

Example

Consider a Hacker table ?

CREATE TABLE Hacker (
    h_id INT,
    h_name VARCHAR(50),
    challenge_id INT,
    score INT
);

INSERT INTO Hacker VALUES
(3, 'Raju', 111, 20),
(2, 'Mithlesh', 111, 80),
(5, 'Rudra', 112, 40),
(5, 'Mohan', 114, 90),
(4, 'Rohan', 112, 30),
(1, 'Sohan', 112, 40);

To rank each hacker within each challenge ?

SELECT challenge_id, h_id, h_name, score,
    DENSE_RANK() OVER (
        PARTITION BY challenge_id
        ORDER BY score DESC
    ) AS 'rank'
FROM Hacker;

PARTITION BY challenge_id groups rows by challenge. ORDER BY score DESC sorts within each partition. DENSE_RANK() assigns ranks tied scores get the same rank.

The output is ?

++-+-+--++++-+-+--++++-+-+--++++-+-+--++++-+-+--++
| challenge_id | h_id | h_name   | score | rank |
++-+-+--++++-+-+--++++-+-+--++++-+-+--++++-+-+--++
|          111 |    2 | Mithlesh |    80 |    1 |
|          111 |    3 | Raju     |    20 |    2 |
|          112 |    5 | Rudra    |    40 |    1 |
|          112 |    1 | Sohan    |    40 |    1 |
|          112 |    4 | Rohan    |    30 |    2 |
|          114 |    5 | Mohan    |    90 |    1 |
++-+-+--++++-+-+--++++-+-+--++++-+-+--++++-+-+--++

Rudra and Sohan both scored 40 in challenge 112, so they share rank 1.

Common Window Functions with PARTITION BY

Conclusion

PARTITION BY groups table rows into windows for window function calculations without collapsing rows like GROUP BY does. It works with ranking, aggregation, and offset functions to perform per-group computations while retaining all individual rows in the result.