SQL Partition: Syntax, Usage, and Examples

SQL partitioning helps you break large datasets into smaller, more manageable sections without physically modifying the table. The PARTITION BY clause lets you apply aggregate functions, ranking functions, and windowing operations within separate partitions instead of across the entire dataset.

How to Use SQL Partition

Use the PARTITION BY clause within window functions such as RANK(), ROW_NUMBER(), and SUM() to group data while keeping partitions distinct. This ensures that calculations restart within each partition rather than spanning the entire table.

Basic Syntax

SELECT column1, column2,
       aggregate_function() OVER (PARTITION BY column3 ORDER BY column4) AS partition_result
FROM table_name;

This syntax groups rows by column3 and orders them by column4. The function runs separately for each partition instead of the whole table.

For example, if you need to rank employees within their respective departments, you can write:

SELECT employee_id, department,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

Use this query to assign ranks to employees based on salary within their departments instead of ranking them across all employees.

When to Use SQL Partition

Speeding Up Queries

Use partitioning to improve query performance by reducing the amount of data scanned. Instead of searching the entire table, SQL processes only the relevant partitions.

SELECT *
FROM sales
WHERE sales_date BETWEEN '2024-01-01' AND '2024-03-31';

If the sales table is partitioned by year, SQL processes only the necessary partitions, making the query run faster.

Ranking and Assigning Row Numbers

Use partitioning to number rows or assign ranks within groups efficiently.

SELECT customer_id, order_date,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_rank
FROM orders;

This assigns a sequential number to each customer’s orders, starting from 1 for their first purchase.

Calculating Running Totals and Averages

Use partitioning to track cumulative totals or moving averages within groups.

SELECT product_id, sale_date, sales_amount,
       SUM(sales_amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS cumulative_sales
FROM sales_data;

This query calculates running sales totals per product, helping you track revenue trends over time.

Examples of SQL Partition

Using PARTITION BY with SUM()

SELECT department, employee_id, salary,
       SUM(salary) OVER (PARTITION BY department) AS total_department_salary
FROM employees;

Use this query to compute each department’s total salary while displaying individual employee details.

Ranking Rows Within a Partition

SELECT student_id, class, score,
       RANK() OVER (PARTITION BY class ORDER BY score DESC) AS class_rank
FROM student_scores;

Use this query to assign rankings to students based on their scores, but only within their respective classes.

Using ROW_NUMBER() with PARTITION BY

SELECT customer_id, purchase_date,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY purchase_date) AS purchase_order
FROM purchases;

Use this query to give each customer’s purchases a sequential number, showing their order history.

Applying Running Totals Within Partitions

SELECT region, month, revenue,
       SUM(revenue) OVER (PARTITION BY region ORDER BY month) AS cumulative_revenue
FROM sales_data;

Use this query to keep a running total of revenue per region, making it easier to analyze trends.

Learn More About SQL Partition

Partitioning Tables for Performance Optimization

Use table partitioning to improve performance when working with large tables. Instead of searching the entire table, SQL looks at only the relevant partitions.

CREATE PARTITION FUNCTION sales_partition_func (DATE)
AS RANGE RIGHT FOR VALUES ('2023-12-31', '2024-12-31');

This function partitions sales data by year.

Using Rank with PARTITION BY

Use PARTITION BY with ranking functions like RANK() and DENSE_RANK() to rank records within groups.

SELECT city, store_id, total_sales,
       DENSE_RANK() OVER (PARTITION BY city ORDER BY total_sales DESC) AS sales_rank
FROM store_sales;

Use this query to assign sales rankings to stores, but only within their respective cities.

Checking if a Table Is Partitioned in SQL Server

Use the following query to check whether a table is partitioned in SQL Server:

SELECT name, type_desc
FROM sys.partitions
WHERE object_id = OBJECT_ID('partitioned_table');

This returns partition details for the specified table.

Partitioning allows you to organize data efficiently, speed up queries, and simplify calculations like ranking and cumulative totals. Whether you're working with large datasets or need structured aggregations, using SQL partitioning can make your queries more efficient and easier to manage.