PARTITION BY Clause
SQL · Window Functions
Theory
PARTITION BY divides the rows into groups, and the window function is calculated separately inside each group. It works like GROUP BY in deciding which rows belong together, but it does not merge them: every row stays, and each row simply sees only the rows of its own partition.
For example, MAX(salary) OVER (PARTITION BY dept) returns 75000 on every IT row, 45000 on the HR row and 52000 on every Sales row. You can then compare each person to their department, as in dept_max - salary to show how far below the top they are. You can partition by several columns, such as PARTITION BY region, year. When ORDER BY is also given, the numbering or running total restarts at the start of each partition. If you leave PARTITION BY out, all rows form one single partition.
Why we use it
Real data is grouped: departments, customers, regions, months. PARTITION BY lets you compute rankings, totals and comparisons per group in one pass while keeping the detail rows, instead of running a separate query for each group.
When to use it
Use PARTITION BY whenever the calculation should restart or be separate per group (rank within department, running total per customer). Leave it out when the whole result is one group. Use GROUP BY instead if you only need one row per group.
How it works
- Rows arrive after WHERE / GROUP BY
- Rows are split by the PARTITION BY columns
- Each partition is processed on its own
- ORDER BY inside OVER? Yes: Walk rows in order per partition. No: Row sees whole partition.
- Compute the function for each row in its partition
- All rows returned with the per-partition result
Syntax
function() OVER (PARTITION BY column)Example
SELECT name, department, salary,
MAX(salary) OVER (PARTITION BY department) AS dept_max
FROM employees;Key points
- Like GROUP BY but keeps individual rows
- Omit it to treat all rows as one partition
- Can list several columns: PARTITION BY region, year
- Numbering and running totals restart in each partition
- Different window functions in one query can use different partitions