PARTITION BY Clause
What is PARTITION BY?
PARTITION BY divides the result set into groups (partitions) for window function calculations. Each partition is processed independently, like a GROUP BY for window functions but without collapsing rows.
Syntax
SELECT column,
window_function() OVER (
PARTITION BY partition_column
ORDER BY sort_column
)
FROM table_name;When to Use
- Ranking rows within each category or group
- Computing per-group aggregates alongside detail rows
- Running totals, averages, or counts per partition
Key Points
- Groups Without Collapsing — Unlike GROUP BY, PARTITION BY keeps all rows visible.
- Independent Windows — Each partition has its own set of row numbers, ranks, etc.
- Multiple Columns — You can partition by multiple columns: PARTITION BY col1, col2.
- Omitting PARTITION BY — Without it, the entire result set is treated as one partition.
- Combined with ORDER BY — ORDER BY within the OVER clause determines row order inside each partition.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
PARTITION BY Clause Challenge
Write a query that solve this task: assign row numbers within each grade group.
Expected result
Each student with a row number starting from 1 within their grade, ordered by age.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is PARTITION BY?
Press Run to execute the query once the engine is ready.
More Examples
Running total per course
Show a running count of enrollments per course.
Press Run to execute the query once the engine is ready.
Average price per category alongside each product
Show each product with the average price of its category.
Press Run to execute the query once the engine is ready.