PARTITION BY Clause | When & How to Use the PARTITION BY in SQL

In this article, I am going to discuss one of the most necessary topics PARTITION BY Clause, when and how to use the SQL partition by clause. Furthermore, compare it to using the GROUP BY clause. At the heart of each window function call is an OVER clause that describes how the windows of the records are built. Within the SQL OVER clause, there may be an optional or non-mandatory PARTITION BY subclause that describes the criteria for identifying which records to include in every window. Read on and take a necessary step in growing your SQL skills!. I hope you discover this article useful and enjoy it!

What is Partitioned by in SQL?

The following article provides an outline of PARTITION BY in SQL. The PARTITION BY is utilized to divide the result set into partitions. After that, it performs computation on every data subset of partitioned data. We use the ‘partition by’ clause to describe the partition in the table. The ‘partition by ‘clause is utilized along with the sub-clause ‘over’. Here, we use window functions to operate the partition separately and recalculate the data subset. Furthermore, the window functions are defined as RANK (), LEAD (), COUNT (), MIN (), MAX () and ROUND(), etc. Thus, the ‘partition by ‘clause is a scalar subquery. Which always returns a single value.


Here expression, we are able to use two or more columns to partition the data result set. Furthermore, expression_1 and expression_2 only refer to the columns by the ‘FROM’ clause. We can not refer to the alias in the select statement.

How to Implement PARTITION BY in SQL?

Let us take the two tables below and the implementation

Below are the "Parcel" and "borrow" tables. Furthermore, once we apply the ‘PARTITION BY’ in SQL to join we get the common rows between two tables.

Parcel Table

Parcel_number Trip_Number Amount No_of_Days
1 99 15 3500 11
2 111 18 4000 14
3 120 24 4400 21
4 151 32 5100 17
5 211 41 5500 12

The given Below is the partition by condition applied as per the 'Amount' partitioning by the 'Trip_Number'.


Borrow Table

Borrow_ID Borrow_Status State Borrow_Amount App_Date
11 Open Uttar Pradesh 250000 2016-02-15
14 Close Ranchi 300000 2016-03-21
21 Approved Delhi 410000 2016-04-04
16 Pending Patna 350000 2016-05-25
34 Open Bangalore 220000 2016-06-18
44 Rejected Ranchi 370000 2016-07-28
51 Close Agra 230000 2016-08-10

The given below the partition by condition is applied as per the 'Borrow_Amount' partitioning by the 'Borrow_ID'.



Here, we utilized ROW_NUMBER for the purpose of paging in SQL Server. Furthermore, it is utilized to provide the consecutive numbers for the rows in the result set by the 'SQL ORDER' clause. The sequence starts from 1.

Not essentially, here, we required a 'partition by' clause while we will use the row_number concept.