PARTITION BY for grouping windows in PostgreSQL - Step-by-Step Execution

Concept Flow - PARTITION BY for grouping windows

Start with full table

↓

Define window with PARTITION BY

↓

Split rows into groups by PARTITION BY column

↓

Apply window function within each group

↓

Combine results preserving original row order

↓

Output result with window function values per row

The PARTITION BY clause splits the table into groups. Then the window function runs separately on each group, keeping the original row order.

Execution Sample

PostgreSQL

SELECT dept, emp, salary,
       RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank
FROM employees;

This query ranks employees by salary within each department separately.

Execution Table

Step	Row (dept, emp, salary)	Partition Group	Order in Group	RANK() Value
1	(Sales, Alice, 7000)	Sales	1	1
2	(Sales, Bob, 6000)	Sales	2	2
3	(Sales, Carol, 6000)	Sales	3	2
4	(HR, Dave, 8000)	HR	1	1
5	(HR, Eve, 7500)	HR	2	2
6	(HR, Frank, 7000)	HR	3	3
7	(IT, Grace, 9000)	IT	1	1
8	(IT, Heidi, 8500)	IT	2	2
9	(IT, Ivan, 8500)	IT	3	2
Exit	All rows processed	-	-	Window function applied per partition

💡 All rows processed; RANK() calculated within each department partition.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	After 6	After 7	After 8	After 9	Final
Current Row	None	(Sales, Alice, 7000)	(Sales, Bob, 6000)	(Sales, Carol, 6000)	(HR, Dave, 8000)	(HR, Eve, 7500)	(HR, Frank, 7000)	(IT, Grace, 9000)	(IT, Heidi, 8500)	(IT, Ivan, 8500)	All rows processed
Partition	None	Sales	Sales	Sales	HR	HR	HR	IT	IT	IT	All partitions assigned
Rank	None	1	2	2	1	2	3	1	2	2	Ranks assigned per partition

Key Moments - 3 Insights

Why do some employees have the same rank number in the same department?

Because RANK() assigns the same rank to rows with the same salary within the partition, as shown in rows 2 and 3 for Sales and rows 8 and 9 for IT in the execution_table.

Does PARTITION BY change the order of rows in the final output?

No, PARTITION BY groups rows for the window function but the original row order is preserved in the output, as seen in the execution_table where rows appear in original order.

What happens if PARTITION BY is omitted?

The window function runs over the entire table as one group, so ranks would be assigned globally, not per department.

Visual Quiz - 3 Questions

Look at the execution_table, what is the RANK() value for (HR, Eve, 7500)?

A 3

B 1

C 2

D None

💡 Hint

Check the row with (HR, Eve, 7500) in the execution_table under the RANK() Value column.

At which step does the partition change from Sales to HR?

A Step 4

B Step 5

C Step 3

D Step 6

💡 Hint

Look at the Partition Group column in the execution_table to see when it switches from Sales to HR.

If we remove PARTITION BY dept, how would the ranks change?

A Ranks would reset for each employee

B Ranks would be assigned across all employees regardless of department

C Ranks would be all 1

D Ranks would be assigned randomly

💡 Hint

Recall that PARTITION BY groups rows; removing it means one big group for ranking.

Concept Snapshot

PARTITION BY splits rows into groups for window functions.
Window functions run separately in each group.
Original row order stays the same.
Example: RANK() OVER (PARTITION BY col ORDER BY col2).
Same values get same rank within partition.
Without PARTITION BY, function runs on whole table.

Full Transcript

The PARTITION BY clause in SQL divides the table into groups based on the specified column(s). Then, window functions like RANK() operate within each group independently. This means each partition gets its own ranking or calculation. The original order of rows is preserved in the output. For example, ranking employees by salary within each department uses PARTITION BY department. Rows with the same salary get the same rank. If PARTITION BY is omitted, the window function treats the entire table as one group. This visual trace shows each row's partition, order within the partition, and the rank assigned step-by-step.