---
title: "Partition by"
url: "https://smartfeeds.productfruits.help/en/article/partition-by"
language: "en"
last_updated: "2025-04-18T14:30:18.242Z"
---

# Partition by

The **PARTITION BY** function is widely used in SQL and data processing to group and analyze data. It is particularly useful for dividing data into partitions that can then be aggregated or analyzed in specific ways. This article will explain the theory behind the **PARTITION BY** function, how it works with other SQL functions like **ROW\_NUMBER** and **ORDER BY**, and how to implement it in real-world use cases.

This article is for data analysts, and anyone working with SQL who wants to better understand how to partition data for analysis and manipulation.

## Prerequisites

-   Basic knowledge of SQL.
-   Understanding of data aggregation.

* * *

## Table of Contents

1.  [Definition](https://smartfeeds.productfruits.help/en/article/partition-by#definition)
2.  [How to Use](https://smartfeeds.productfruits.help/en/article/partition-by#how-to-use-it)
3.  [Quick Answers to Common Issues](https://smartfeeds.productfruits.help/en/article/partition-by#how-to-use-it)
4.  [Related Articles](https://smartfeeds.productfruits.help/en/article/partition-by#related-articles)

* * *

## Definition

The `**PARTITION BY**` function is used to split data into partitions for further analysis. It works together with the `**OVER**` function to apply window functions. The function is often used with `**ROW_NUMBER**` or `**ORDER BY**` to rank, group, or order data within each partition.

`**ROW_NUMBER**`: Assigns sequential numbers to rows within each partition.  
`**ORDER BY**`: Orders the rows within each partition based on a specific column (e.g., price).

### **Examples**

#### **Case 1**: Partitioning by ID

-   **Formula:** `ROW_NUMBER() OVER (PARTITION BY id)`
-   **Result:** Products with the same ID will be grouped together, and each product within the group will receive a unique row number.  
     

| id | row\_number |
| --- | --- |
| 123 | 1 |
| 123 | 2 |
| 456 | 1 |
| 789 | 1 |

-   **Purpose:** This helps identify products that belong to the same group based

#### **Case 2**: Partitioning by Product Type and Ordering by Price

-   **Formula:** `ROW_NUMBER() OVER (PARTITION BY product_type ORDER BY price)`
-   **Result:** Products with the same **product\_type** are grouped, then ordered by their price, and numbered within their group.

| Product\_type | price | row\_number |
| --- | --- | --- |
| shoes | 5.00 | 1 |
| shoes | 8.00 | 2 |
| top | 7.00 | 1 |
| bottom | 5.00 | 1 |
| shoes | 15.00 | 3 |

-   **Purpose:** The row number helps identify the cheapest and most expensive products within each type.

## How to use it

```plaintext
PARTITION BY expression
```

-   Used to define how data should be grouped into partitions.

```plaintext
OVER (PARTITION BY expression)
```

-   This is the window function that applies the partition logic.

```plaintext
ROW_NUMBER() OVER (PARTITION BY expression)
```

-   Assigns a unique sequential integer to rows within a partition.

```plaintext
ROW_NUMBER() OVER (PARTITION BY expression ORDER BY column)
```

-   Similar to the above, but with an order defined within each partition.

* * *

## Quick Answers to Common Issues

### **Why doesn't the PARTITION BY function work?**

Ensure the correct syntax is used and that the **OVER** function is included.

### **How do I prevent rows from being duplicated in the output?**

We have a dedicated article for duplicated rows; you can choose to use a deduplication process or try to do it manually in the SQL editor

### **What happens if there are duplicates in the data?**

Use the `**ROW_NUMBER()**` function to eliminate duplicates by assigning a unique number within each partition. Filter the rows based on the number. You can also see our article [Deduplication](https://smartfeeds.productfruits.help/en/article/deduplication)

### **Can I use multiple PARTITION BY clauses?**

Yes, you can partition by multiple columns. The data will be grouped based on the combination of the specified columns.

### **Why might partition ordering be unexpected when using multiple window functions with different** `**ORDER BY**` **clauses?**

This is a subtle issue related to SQL query execution plans. If your query defines multiple window functions (like `ROW_NUMBER()`) targeting different columns or purposes, and these functions use different `ORDER BY` clauses within their `OVER()` sections (e.g., one orders by `cost`, another orders by `brand`), the actual ordering applied within the partitions of the *later* functions might be unexpectedly influenced by the `ORDER BY` clause of the *earlier* ones.

For example, a partition intended to be ordered solely by `brand` might end up being ordered first by `cost` (from an earlier window function) and then by `brand`. This behavior can vary between database systems and specific query structures. There isn't a straightforward, universal solution, so awareness is important when designing queries with multiple window functions that have conflicting `ORDER BY` requirements. Carefully test your results in such scenarios.

* * *

## Related Articles

For further assistance with SmartFeeds, consider reviewing these articles:

-   [Deduplication](https://smartfeeds.productfruits.help/en/article/deduplication)
-   [Regex](https://smartfeeds.productfruits.help/en/article/regex)
-   [Mathematical Formulas](https://smartfeeds.productfruits.help/en/article/mathematical-formulas)
-   [Text Processing Formulas](https://smartfeeds.productfruits.help/en/article/text-processing-formulas)

For additional help, feel free to reach out via our [Contact Us](https://smartfeeds.productfruits.help/en/article/contact-us) page.