What is PARTITION BY in SQL?

Learn via video course
FREE
View all courses
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning

The PARTITION BY clause is a subclause to OVER(). It divides the resultant rows into different partitions based on the specified columns' values. Then the window function is applied to each partition made and gives the results in the form of a separate column.

  • The partitions made by the clause are referred to as 'Window'.
  • Window functions that can be used with this clause are RANK(), LAG(), LEAD(), COUNT(), MAX() etc.

How to Implement PARTITION BY Clause in SQL?

The PARTITION BY clause is used with the OVER() clause to form clusters based on a specified column.

Syntax of PARTITION BY in SQL:

Standard SQL allows only column names to be put as expression1, and expression2. OrderClause and frameClause are optional, and can only be added when required.

Example:

Here, we've created a table StudentMarks with columns Id, Name, Subject, and Marks.

example-partition-by

Output:

output-partition-by

As a result, PARTITION BY has calculated the average marks for the students where each window is made on the Subject.

Unlike GROUP BY, it returns each row of the table with the calculated results.

Learn More

Conclusion

In this article, we have learned about:

  • Aggregate and Window functions play a vital role in SQL.
  • PARTITION BY clause in SQL is used to make different windows and functions are then applied to it.
  • PARTITION BY is a scalar subquery that always returns a single value for each row.

See Also: