A PARTITION BY clause is used to partition rows of table into groups. It is useful when we have to perform a calculation on individual rows of a group using other rows of that group.
- It is always used inside OVER() clause.
- The partition formed by partition clause are also known as Window.
- This clause works on windows functions only. Like- RANK(), LEAD(), LAG() etc.
- If this clause is omitted in OVER() clause, then whole table is considered as a single partition.
The syntax for Partition clause is-
Window_function ( expression ) Over ( partition by expr [order_clause] [frame_clause] )
Here, order_clause and frame_clause are optional.
expr can be column names or built-in functions in MySQL.
But, standard SQL permits only column names in expr.
Consider, a table “Hacker“:
We have to find the rank of hackers in each challenge. That means we have to list all participated hackers of a challenge along with their rank in that challenge.
select challenge_id, h_id, h_name, score, dense_rank() over ( partition by challenge_id order by score desc ) as "rank", from hacker;
In the above query, partition by clause will partition table into groups that are having same challenge_id.
order by will arrange the hackers of each partition in descending order by “scores”.
over() clause defines how to partition and order rows of table, which is to be processed by window function rank().
dense_rank() is a window function, which will assign rank in ordered partition of challenges. If two hackers have same scores then they will be assigned same rank.
Thus, we get list of all hackers along with their ranks in the individual challenges.
Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.
- Difference between Having clause and Group by clause
- PHP | MySQL WHERE Clause
- MySQL | Common MySQL Queries
- SQL | Distinct Clause
- SQL | WHERE Clause
- SQL | SELECT TOP Clause
- SQL | Union Clause
- SQL | WITH clause
- Having vs Where Clause in SQL
- SQL | ON Clause
- Combining aggregate and non-aggregate values in SQL using Joins and Over clause
- Update clause in Cassandra
- SQL query using COUNT and HAVING clause
- Difference between Where and Having Clause in SQL
- Difference between order by and group by clause in SQL
- Difference between From and Where Clause in SQL
- Distinct clause in MS SQL Server
- Where clause in MS SQL Server
- Having clause in MS SQL Server
- Group by clause in MS SQL Server
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.