SQL PARTITION

What is SQL PARTITION?

PARTITION BY divides result sets into logical groups so window functions operate independently on each group.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL PARTITION: PostgreSQL • MySQL 8+ • SQL Server • Oracle • SQLite 3.25+ • Snowflake • BigQuery • Redshift and most modern analytic databases

SQL PARTITION Full Explanation

PARTITION BY is a sub-clause of the OVER() clause used with window (analytic) functions. It virtually splits the result set into partitions—similar to GROUP BY—but without collapsing rows. Each window function then runs independently inside every partition while retaining the original row granularity. If omitted, the entire result set is treated as one partition. PARTITION BY does not create physical storage partitions and has no effect on table design. It is evaluated after WHERE, GROUP BY, and HAVING, but before ORDER BY at the query level. Multiple window functions in the same SELECT can share or define different PARTITION BY clauses. In Standard SQL, PARTITION BY may be combined with ORDER BY and frame specifications (ROWS or RANGE) to further refine the window. Caveats: columns referenced must be visible to the SELECT list (either base columns or derived aliases defined earlier in the same SELECT item list cannot be reused inside the window); large partitions can consume memory; wrong partitioning may lead to incorrect analytic results.

SQL PARTITION Syntax

<window_function>() OVER (
    PARTITION BY <expr1>[, <expr2> ...]
    [ORDER BY <expr3> [ASC|DESC] [, ...]]
    [<frame_clause>]
);

SQL PARTITION Parameters

expr1, expr2, ... (any) - Expressions that define the logical partition.
expr3, ... (any) - Optional sort order inside each partition.
frame_clause (text) - Optional ROWS/RANGE clause limiting the window frame.

Example Queries Using SQL PARTITION

-- Running total of sales per customer
SELECT
    customer_id,
    order_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders;

-- Rank employees by salary inside each department
SELECT
    department_id,
    employee_name,
    salary,
    RANK() OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees;

Expected Output Using SQL PARTITION

Each row includes an additional column generated by the window function
The calculation is performed independently for every partition defined by PARTITION BY, preserving all original rows

Use Cases with SQL PARTITION

Calculate running totals, moving averages, and cumulative sums per business entity
Rank or dense-rank rows inside logical groups such as departments or regions
Compute percentages of total within each category
Identify first/last values or gaps within partitions
Build cohort or retention analyses where metrics reset per cohort

Common Mistakes with SQL PARTITION

Confusing PARTITION BY with physical table partitioning
Forgetting ORDER BY, leading to non-deterministic results for ranking or cumulative functions
Referencing column aliases that are defined in the same SELECT list
Using GROUP BY when the intent was window analytics, unintentionally collapsing rows
Expecting performance benefits on huge tables; PARTITION BY is purely logical

First Introduced In

SQL:2003 (window function standard)

Frequently Asked Questions

What problems does PARTITION BY solve?

It lets you run aggregate-style calculations like sums, counts, or ranks while keeping every row, eliminating extra joins or subqueries for many analytics tasks.

Can I reference an alias inside the same PARTITION BY clause?

No. Use the original column name or wrap the SELECT in a subquery where the alias is materialized, then reference it.

How do I limit the window to recent rows?

Add a frame clause such as ROWS BETWEEN 3 PRECEDING AND CURRENT ROW inside the OVER() specification.

Is PARTITION BY mandatory in an OVER clause?

No. If you omit it, the entire result set is treated as a single partition.