PARTITION BY Explained in SQL

PARTITION BY vs GROUP BY

Both create the exact same logical groups from the same column values. The difference is entirely in what happens next. GROUP BY collapses each group into a single summary row, discarding the individual rows that made it up. PARTITION BY, used inside a window function's OVER() clause, keeps every row and simply computes the value within each group, attaching it back to every row in that group.

For the full walkthrough of this exact distinction with a worked example, see SQL Window Function Interview Questions (Q2 and Q3) and the broader introduction in Window Functions Explained Simply.

Basic example: reset a calculation per group

SELECT name, department, salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

Every employee keeps their own row, but dept_avg is calculated separately for Engineering and Sales — AVG(salary) never mixes the two departments together, exactly as if two entirely separate queries had been run and merged back onto their original rows.

PARTITION BY on multiple columns

Just like GROUP BY, PARTITION BY accepts more than one column, creating one partition per distinct combination:

SELECT region, product_category, month, revenue,
  SUM(revenue) OVER (
    PARTITION BY region, product_category
  ) AS category_total_in_region
FROM sales;

category_total_in_region sums revenue within each unique (region, product_category) pair — West/Electronics gets its own total, separate from East/Electronics, even though both share the same product_category value.

PARTITION BY combined with ORDER BY

Adding ORDER BY inside the same OVER() clause changes the window from "the whole partition at once" to "everything from the start of the partition through the current row" — the standard way to build a running total per group instead of one flat running total across everything:

SELECT customer_id, order_date, total,
  SUM(total) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS running_total_per_customer
FROM orders;

Customer 102's running total starts fresh at 90 instead of continuing from customer 101's 80 — the partition boundary resets the running calculation. This same interaction applies directly to LAG() and LEAD(), covered with more examples in SQL LEAD and LAG Functions Example.

The top-N-per-group pattern

This is the single most common real-world use of PARTITION BY: find the top 2 highest-paid employees in each department, or the best-selling product per category. It combines PARTITION BY with a ranking function:

SELECT * FROM (
  SELECT name, department, salary,
    ROW_NUMBER() OVER (
      PARTITION BY department
      ORDER BY salary DESC
    ) AS rnk
  FROM employees
) ranked
WHERE rnk <= 2;

PARTITION BY department makes the ranking restart at 1 for every department, so "rank 2" means second-highest within that department, not second-highest company-wide. The outer query is required because a window function's result — including rnk here — can't be filtered directly in the same query's WHERE, the same restriction covered in SQL Window Function Interview Questions. Whether to use ROW_NUMBER(), RANK(), or DENSE_RANK() here depends on how ties should behave — see ROW_NUMBER vs RANK vs DENSE_RANK.

No PARTITION BY at all

PARTITION BY is optional. Without it, a window function treats the entire result set as a single partition — every row shares the same window, and a calculation like AVG(salary) OVER () attaches the company-wide average to every single row, with no grouping at all.

Common mistake: expecting rows to collapse

The most common confusion is expecting PARTITION BY to behave like GROUP BY and reduce the row count. It never does — a query with PARTITION BY and no separate GROUP BY always returns exactly as many rows as it would without the window function at all. If fewer, summarized rows are actually the goal, GROUP BY is the right tool, not PARTITION BY — see SQL GROUP BY Without Aggregate Function for that side of the comparison.