PARTITION BY

The PARTITION BY clause is used with window functions to divide rows into groups, allowing each group to be processed independently.

It works similarly to GROUP BY, but unlike GROUP BY, it does not collapse rows. Instead, it retains all rows while logically segmenting them for window function calculations.

Syntax

You can use PARTITION BY to group rows into partitions.

PARTITION BY syntax

SELECT column,
  window_function(...) OVER (
    PARTITION BY group_column
    ORDER BY sort_column
  ) AS result
FROM table;

• PARTITION BY splits rows into groups.
• ORDER BY sorts rows within each group.

Example: Rank Students Within Each Class

Assume we have the following table:

scores

name	class	score
Alex	A	92
Sara	A	95
Daniel	A	88
Mia	B	90
John	B	85
Emma	B	93

We want to rank students within each class by their score.

Using PARTITION BY

SELECT name,
  class,
  score,
  RANK() OVER (
    PARTITION BY class
    ORDER BY score DESC
  ) AS rank_in_class
FROM scores;

The query returns the following:

Result:

name	class	score	rank_in_class
Sara	A	95	1
Alex	A	92	2
Daniel	A	88	3
Emma	B	93	1
Mia	B	90	2
John	B	85	3

Here, each class has its own ranking system because of PARTITION BY class.

In general, PARTITION BY splits rows into groups so window functions calculate results within each group rather than across the entire table.

Want to learn more?

Join CodeFriends Plus membership or enroll in a course to start your journey.

Learn moreSign In