PARTITION BY Clause
beginnerwindow-functions
2 min read
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.
Practice challengeGuided learning mode
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?
Initializing database...Each run starts from fresh sample data.
More Examples
Running total per course
Show a running count of enrollments per course.
Initializing database...Each run starts from fresh sample data.
Average price per category alongside each product
Show each product with the average price of its category.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
Is PARTITION BY the same as GROUP BY?
No. GROUP BY collapses rows into one per group. PARTITION BY keeps all rows and computes the window function within each group.
Can I use PARTITION BY without ORDER BY?
Yes. Without ORDER BY, the window function considers all rows in the partition at once (e.g., AVG over the entire group).