SQL PARTITION BY: Complete Guide with Examples (2026)

What Is PARTITION BY in SQL?

PARTITION BY is a clause used exclusively inside window functions (also called analytic functions). It divides the rows of a query result into separate groups — called partitions — and the window function computes its value independently within each partition.

The critical difference from GROUP BY: PARTITION BY does not collapse rows. Every row in the input still appears in the output. You get the group-level calculation added as an extra column alongside the individual row data.

This makes PARTITION BY essential for analytics queries where you need both detail and aggregates in the same result — things like ranking sales reps within their region, computing each product's contribution to its category total, or flagging duplicate records.

For a broader introduction to window functions, see the SQL Window Functions Complete Guide.

Syntax

The general form of a window function with PARTITION BY is:

function_name(expression) OVER (
    PARTITION BY column1 [, column2, ...]
    [ORDER BY column3 [ASC|DESC]]
    [frame_clause]
)

• function_name — any window function: ROW_NUMBER, RANK, DENSE_RANK, SUM, AVG, COUNT, LAG, LEAD, etc.
• PARTITION BY — one or more columns that define the partitions. The function restarts for each unique combination of these column values.
• ORDER BY — required for ranking functions; optional for aggregate functions.
• frame_clause — optional; narrows the window further (e.g., for running totals).

Here is the sample data used throughout this guide:

CREATE TABLE sales (
    id          INT PRIMARY KEY,
    salesperson VARCHAR(50),
    region      VARCHAR(20),
    category    VARCHAR(20),
    amount      DECIMAL(10,2),
    sale_date   DATE
);

INSERT INTO sales VALUES
(1,  'Alice', 'North', 'Software', 700.00, '2026-01-15'),
(2,  'Bob',   'North', 'Hardware', 350.00, '2026-01-20'),
(3,  'Alice', 'North', 'Software', 500.00, '2026-02-10'),
(4,  'Carol', 'South', 'Software', 450.00, '2026-02-15'),
(5,  'Bob',   'North', 'Hardware', 600.00, '2026-02-28'),
(6,  'Carol', 'South', 'Hardware', 800.00, '2026-03-05'),
(7,  'Dave',  'South', 'Software', 300.00, '2026-03-10'),
(8,  'Alice', 'North', 'Software', 550.00, '2026-03-15'),
(9,  'Dave',  'South', 'Hardware', 650.00, '2026-03-20'),
(10, 'Bob',   'North', 'Hardware', 400.00, '2026-03-25');

PARTITION BY vs GROUP BY

This is the most important distinction to understand. The two clauses look similar but produce fundamentally different output shapes.

Feature	GROUP BY	PARTITION BY
Output rows	One per group	One per input row (unchanged)
Collapses rows?	Yes	No
Where used	SELECT / HAVING clause	Inside OVER() only
Row-level detail	Lost	Preserved
Typical use	Summary reports	Per-row analytics alongside detail

A concrete comparison using the same aggregation goal:

-- GROUP BY: one row per region, individual rows disappear
SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region;

-- PARTITION BY: every sale row kept, region total added alongside
SELECT
    salesperson,
    region,
    amount,
    SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales;

The GROUP BY query returns two rows (North, South). The PARTITION BY query returns all ten rows, each annotated with its region total.

PARTITION BY with ROW_NUMBER, RANK, and DENSE_RANK

Ranking functions are the most common use case for PARTITION BY. Adding PARTITION BY makes the ranking restart for each group rather than running globally across all rows.

-- Rank each salesperson by amount within their region
SELECT
    salesperson,
    region,
    amount,
    sale_date,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS row_num,
    RANK()       OVER (PARTITION BY region ORDER BY amount DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS dense_rnk
FROM sales
ORDER BY region, amount DESC;

With PARTITION BY region, the ranking resets to 1 at the start of each region. Without it, the ranking would run across all rows globally and would not give you per-region positions.

The three functions differ in how they handle tied values:

Function	Ties get same rank?	Skips numbers after ties?
ROW_NUMBER	No — always unique	N/A
RANK	Yes	Yes (1, 2, 2, 4)
DENSE_RANK	Yes	No (1, 2, 2, 3)

For more detail on ROW_NUMBER specifically, see the SQL ROW_NUMBER guide.

PARTITION BY with SUM and AVG (Running Totals)

You can combine PARTITION BY with aggregate functions to compute group-level values row by row. Adding a frame clause turns a simple aggregate into a running total.

-- Total sales per region alongside each row
SELECT
    salesperson,
    region,
    amount,
    SUM(amount) OVER (PARTITION BY region)           AS region_total,
    AVG(amount) OVER (PARTITION BY region)           AS region_avg,
    ROUND(amount / SUM(amount) OVER (PARTITION BY region) * 100, 1) AS pct_of_region
FROM sales
ORDER BY region, salesperson;

-- Running total within each region, ordered by date
SELECT
    salesperson,
    region,
    sale_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY region
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM sales
ORDER BY region, sale_date;

The frame clause ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW tells SQL to sum from the first row in the partition up to (and including) the current row. Without the frame clause, SUM(...) OVER (PARTITION BY region ORDER BY sale_date) produces the same result in most databases, but being explicit avoids database-specific behavior differences.

Multiple Columns in PARTITION BY

You can partition on more than one column. The window function resets for every unique combination of values across all listed columns.

-- Rank sales within each region-category combination
SELECT
    salesperson,
    region,
    category,
    amount,
    ROW_NUMBER() OVER (
        PARTITION BY region, category
        ORDER BY amount DESC
    ) AS rank_in_group
FROM sales
ORDER BY region, category, rank_in_group;

This query creates four partitions: North/Software, North/Hardware, South/Software, South/Hardware. The ranking restarts at 1 inside each combination independently.

Real-World Examples

Sales ranking per region

A classic reporting pattern: find the top performer in each region.

-- Top salesperson per region by total revenue
WITH regional_totals AS (
    SELECT
        salesperson,
        region,
        SUM(amount) AS total,
        RANK() OVER (
            PARTITION BY region
            ORDER BY SUM(amount) DESC
        ) AS rnk
    FROM sales
    GROUP BY salesperson, region
)
SELECT salesperson, region, total
FROM regional_totals
WHERE rnk = 1;

Deduplication

One of the most practical uses of PARTITION BY is identifying and removing duplicate rows. Assign ROW_NUMBER() partitioned by the columns that define uniqueness, then delete or filter any row where the number is greater than 1.

-- Flag duplicate rows: same salesperson, same date, same amount
WITH dedup AS (
    SELECT
        id,
        salesperson,
        sale_date,
        amount,
        ROW_NUMBER() OVER (
            PARTITION BY salesperson, sale_date, amount
            ORDER BY id
        ) AS rn
    FROM sales
)
-- Preview duplicates before deleting
SELECT * FROM dedup WHERE rn > 1;

-- Delete the duplicates
DELETE FROM sales
WHERE id IN (
    SELECT id FROM dedup WHERE rn > 1
);

The ORDER BY id inside the window ensures that the lowest id (the original row) gets rn = 1 and is kept, while later duplicates get rn > 1 and are removed.

Frequently Asked Questions

What does PARTITION BY do in SQL?

PARTITION BY divides the result set into groups before a window function is applied. The function computes independently within each group, restarting its calculation at the start of every new partition. All rows are preserved in the output — nothing is collapsed or hidden.

What is the difference between PARTITION BY and GROUP BY?

GROUP BY collapses multiple rows into one summary row per group and is used in the main SELECT clause. PARTITION BY is used inside OVER() and keeps all rows intact while adding a computed column. Use GROUP BY for summary reports; use PARTITION BY when you need both individual rows and group-level values in the same query.

Can I use PARTITION BY with multiple columns?

Yes. List multiple columns separated by commas: PARTITION BY region, category. The window function resets for every unique combination of the listed column values.

Is PARTITION BY required in window functions?

No. PARTITION BY is optional. If omitted, the window function treats the entire result set as one partition. For example, ROW_NUMBER() OVER (ORDER BY salary DESC) numbers all rows globally without any grouping.