SQL PARTITION BY: Examples, vs GROUP BY, Pitfalls

TL;DR: PARTITION BY in SQL splits rows into groups for a window function inside OVER (...), without collapsing them. SUM(amount) OVER (PARTITION BY customer_id) puts each customer's total on every one of that customer's rows, where GROUP BY customer_id would return one row per customer. Add ORDER BY inside OVER for rankings and running totals, and filter on the result in an outer query, never in WHERE.

PARTITION BY is the clause of a window function's OVER (...) that divides the result set into partitions, so the function is computed separately for each group of rows sharing the same PARTITION BY values. The PostgreSQL manual describes it as dividing "the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s)", with the window function computed "across the rows that fall into the same partition as the current row". Unlike GROUP BY, every input row stays in the output. The syntax is the same in PostgreSQL, MySQL 8.0+, SQL Server 2012+ (for frames), SQLite 3.25+, Oracle, BigQuery and Snowflake. This page shows working examples for the jobs people actually use it for (per-group totals, top N per group, running totals, deduplication, previous-row comparisons), the default frame rule that makes LAST_VALUE look broken, and how to partition by multiple columns.

What does PARTITION BY do in SQL?

PARTITION BY defines which rows a window function looks at for each row. Take an orders table:

id | customer_id | amount

1 | 7 | 100

2 | 7 | 300

3 | 9 | 50

SELECT id,
       customer_id,
       amount,
       SUM(amount) OVER (PARTITION BY customer_id) AS customer_total,
       amount * 100.0 / SUM(amount) OVER (PARTITION BY customer_id) AS pct_of_customer
FROM orders;

id | customer_id | amount | customer_total | pct_of_customer

1 | 7 | 100 | 400 | 25.0

2 | 7 | 300 | 400 | 75.0

3 | 9 | 50 | 50 | 100.0

Each row keeps its own columns and also sees its partition's total. Without PARTITION BY (SUM(amount) OVER ()), the whole result set is one partition and every row gets the grand total, 450.

The full syntax is:

window_function(args) OVER (
  [PARTITION BY expr [, ...]]
  [ORDER BY expr [ASC | DESC] [, ...]]
  [ROWS | RANGE | GROUPS frame_start [AND frame_end]]
)

What is the difference between PARTITION BY and GROUP BY?

GROUP BY collapses each group into one output row; PARTITION BY keeps every row and attaches the group-level result to it.

GROUP BY | PARTITION BY

Rows returned | One per group | One per input row

Where it goes | Query-level clause | Inside OVER (...)

Non-grouped columns | Not allowed in SELECT unless aggregated | Allowed

Functions | Aggregates (SUM, COUNT, AVG) | Aggregates plus ranking/offset (ROW_NUMBER, RANK, LAG, LEAD)

Typical use | Totals per customer | Each order next to its customer's total, rank, or previous order

Evaluation order | Before window functions | After WHERE, GROUP BY and HAVING

They combine: window functions run after GROUP BY, so you can window over aggregated rows. This returns each month's revenue and its share of the year:

SELECT date_trunc('month', created_at) AS month,
       SUM(amount) AS revenue,
       SUM(amount) * 100.0 / SUM(SUM(amount)) OVER (PARTITION BY date_trunc('year', created_at)) AS pct_of_year
FROM orders
GROUP BY 1, date_trunc('year', created_at)
ORDER BY 1;

SUM(SUM(amount)) OVER (...) is valid: the inner SUM is the GROUP BY aggregate, the outer one is the window function. The PostgreSQL manual states the rule: "it is valid to include an aggregate function call in the arguments of a window function, but not vice versa."

How do you get the top N rows per group with PARTITION BY?

Number the rows inside each partition with ROW_NUMBER(), then filter in an outer query:

SELECT *
FROM (
  SELECT id, customer_id, amount, created_at,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC, id) AS rn
  FROM orders
) ranked
WHERE rn <= 3;

You can't write WHERE ROW_NUMBER() OVER (...) <= 3 directly. Window functions "are permitted only in the SELECT list and the ORDER BY clause", per the PostgreSQL manual, because they execute after WHERE, GROUP BY and HAVING. Use a subquery or CTE. (Snowflake, BigQuery and DuckDB also support a QUALIFY clause for this; PostgreSQL, MySQL and SQL Server do not.)

Choose the ranking function by how you want ties handled:

Function | Ties on amount 300, 300, 100 | Use for

ROW_NUMBER() | 1, 2, 3 (tie order arbitrary unless ORDER BY breaks it) | Exactly N rows, dedup

RANK() | 1, 1, 3 | Leaderboards with gaps

DENSE_RANK() | 1, 1, 2 | "Top 3 distinct values"

Add a unique column (id) as the last ORDER BY key so ROW_NUMBER() is deterministic; the PostgreSQL manual notes tied rows are otherwise "numbered in an unspecified order".

How do you calculate a running total with PARTITION BY?

Add ORDER BY inside OVER. The aggregate then covers the partition from its first row up to the current row:

SELECT customer_id,
       created_at,
       amount,
       SUM(amount) OVER (
         PARTITION BY customer_id
         ORDER BY created_at
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS lifetime_value_so_far
FROM orders;

For a 7-row moving average, use ROWS BETWEEN 6 PRECEDING AND CURRENT ROW with AVG(). For a 7-day window on irregular dates, PostgreSQL 11+ supports RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW.

Why does LAST_VALUE or a running total look wrong with ORDER BY?

Because of the default window frame. When OVER has an ORDER BY but no frame clause, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW in PostgreSQL, SQL Server, MySQL and SQLite. Two consequences:

When ORDER BY is omitted, the frame is the whole partition, which is why SUM(amount) OVER (PARTITION BY customer_id) returns the full customer total.

How do you use PARTITION BY with multiple columns?

List the columns separated by commas. A new partition starts for every distinct combination:

SELECT customer_id,
       product_id,
       created_at,
       ROW_NUMBER() OVER (PARTITION BY customer_id, product_id ORDER BY created_at) AS nth_purchase_of_product
FROM order_items;

You can also partition by an expression, such as PARTITION BY customer_id, date_trunc('month', created_at) in PostgreSQL or PARTITION BY customer_id, YEAR(created_at), MONTH(created_at) in MySQL and SQL Server. NULLs are treated as equal for partitioning, so all NULL product_id rows form one partition.

How do you remove duplicate rows with PARTITION BY?

Keep row number 1 per set of duplicate keys. Find them first:

WITH ranked AS (
  SELECT id,
         ROW_NUMBER() OVER (PARTITION BY lower(email) ORDER BY created_at, id) AS rn
  FROM users
)
SELECT * FROM users WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

Review that output, then change the final SELECT * to DELETE (PostgreSQL and MySQL 8.0 accept this form; in MySQL, wrap the CTE in a derived table if you hit error 1093). Run it inside a transaction so you can roll back.

How do you compare a row to the previous row in its group?

LAG() and LEAD() read a value from an earlier or later row in the same partition:

SELECT customer_id,
       created_at,
       amount,
       created_at - LAG(created_at) OVER w AS time_since_previous_order,
       amount - LAG(amount, 1, 0) OVER w   AS change_vs_previous
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY created_at);

The WINDOW clause names a definition once so several functions can reuse it. It is supported in PostgreSQL, MySQL 8.0, SQLite 3.28+ and SQL Server 2022 (16.x) and later. LAG returns NULL on each partition's first row unless you pass a default as the third argument.

Does PARTITION BY work the same in every database?

The core syntax is standard SQL and portable. The differences are in versions and extras:

Database | Window functions since | Notes

PostgreSQL | 8.4 | GROUPS frames, RANGE with interval offsets (11+), FILTER on window aggregates

MySQL | 8.0 | Not available in 5.7 or earlier

SQL Server | 2005 (OVER), frames in 2012 | WINDOW clause in 2022

SQLite | 3.25.0 (2018-09-15) | Frame EXCLUDE and GROUPS from 3.28.0

Snowflake, BigQuery, DuckDB | yes | QUALIFY to filter on window results

Don't confuse window PARTITION BY with table partitioning (CREATE TABLE ... PARTITION BY RANGE (created_at) in PostgreSQL or MySQL). That splits physical storage for large tables and has nothing to do with query results.

Getting the answer without writing the window

Per-customer totals, top 3 orders per account and month-over-month change are the questions behind most PARTITION BY queries. AI for Database connects to PostgreSQL, MySQL or SQL Server, writes the window function from a plain-English question and shows the SQL it ran. Related guides: cohort analysis from your database, CASE WHEN in PostgreSQL, DATEDIFF in PostgreSQL and SaaS churn on Postgres.