PARTITION BY

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

It works similarly to GROUP BY, but unlike GROUP BY, it does not collapse rows. Instead, it keeps all rows and logically segments them for window function calculations.

Syntax

Use PARTITION BY to divide 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

Suppose 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

In this result, each class has its own ranking system because of PARTITION BY class.

Why is PARTITION BY useful?

Use PARTITION BY when you want to:

• Apply calculations within groups (e.g., ranks per class, totals per department)
• Keep individual rows visible while still comparing them to peers
• Perform analytics and reporting where context matters inside groups

Want to learn more?

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

Learn moreSign In