OVER clause with PARTITION BY in SQL - Step-by-Step Execution

Concept Flow - OVER clause with PARTITION BY

Start with full table

↓

Apply PARTITION BY to split rows into groups

↓

Within each group, apply window function

↓

Combine results preserving original row order

↓

Return result set with window function values per row

The OVER clause with PARTITION BY splits the table into groups and applies a window function to each group separately, returning results alongside original rows.

Execution Sample

SQL

SELECT department, employee, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

This query ranks employees by salary within each department.

Execution Table

Step	Row (department, employee, salary)	Partition	Order in Partition	RANK() Result
1	(Sales, Alice, 70000)	Sales	1	1
2	(Sales, Bob, 60000)	Sales	2	2
3	(Sales, Carol, 60000)	Sales	2	2
4	(HR, Dave, 65000)	HR	1	1
5	(HR, Eve, 55000)	HR	2	2
6	(IT, Frank, 80000)	IT	1	1
7	(IT, Grace, 75000)	IT	2	2
8	(IT, Heidi, 75000)	IT	2	2
9	End of rows	-	-	-

💡 All rows processed; ranks assigned within each department partition.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	After 6	After 7	After 8	Final
Current Row	None	(Sales, Alice, 70000)	(Sales, Bob, 60000)	(Sales, Carol, 60000)	(HR, Dave, 65000)	(HR, Eve, 55000)	(IT, Frank, 80000)	(IT, Grace, 75000)	(IT, Heidi, 75000)	None
Partition	None	Sales	Sales	Sales	HR	HR	IT	IT	IT	None
Rank	None	1	2	2	1	2	1	2	2	None

Key Moments - 3 Insights

Why do Bob and Carol have the same rank 2 in the Sales department?

Because they have the same salary and the RANK() function assigns the same rank to ties within the partition, as shown in execution_table rows 2 and 3.

Does the OVER clause reorder the entire table?

No, the OVER clause with PARTITION BY applies the window function within each group but preserves the original row order in the final result, as seen in the execution_table.

What happens if PARTITION BY is omitted?

The window function applies to the entire table as a single group, not partitioned, so ranks would be assigned across all rows together.

Visual Quiz - 3 Questions

Look at the execution_table, what is the rank of Heidi at step 8?

A 2

B 1

C 3

D None

💡 Hint

Check the 'RANK() Result' column at step 8 in the execution_table.

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

A Step 3

B Step 4

C Step 5

D Step 6

💡 Hint

Look at the 'Partition' column in the variable_tracker to see when it changes.

If the ORDER BY salary DESC is changed to ASC, what happens to Alice's rank?

A It becomes 1

B It stays 1

C It becomes the highest rank number in Sales

D It becomes 2

💡 Hint

Ranks depend on ordering; changing DESC to ASC reverses rank order within partitions.

Concept Snapshot

OVER clause with PARTITION BY:
- Splits rows into groups by PARTITION BY columns
- Applies window function (e.g., RANK()) within each group
- Preserves original row order
- Useful for ranking, running totals, etc.
- Syntax: FUNCTION() OVER (PARTITION BY col ORDER BY col2)

Full Transcript

The OVER clause with PARTITION BY divides the table into groups based on specified columns. Then, a window function like RANK() is applied within each group separately. This means each group is ranked or aggregated independently, but the original row order is kept in the output. For example, ranking employees by salary within each department assigns ranks starting at 1 for the highest salary in each department. Ties get the same rank. The execution table shows each row processed, its partition group, order within that group, and the rank assigned. Variable tracking shows how the current row, partition, and rank change step by step. Key moments clarify why ties share ranks and that the table is not reordered globally. The quiz tests understanding of ranks, partition changes, and effects of ordering direction. This concept is essential for advanced SQL queries that analyze data in groups without collapsing rows.