Window Functions in SQL

5 min readFeb 22, 2025

Hello Folks,

The chances are high that you will encounter the Window Functions at some point of time if you work with or are willing to work with Databases or in the Business Intelligence (BI) domain. Window Functions are commonly used for ordering of certain items on basis of specific criterias, calculating moving averages, etc. When working with BI, often we need to display top 5 products or bottom 5 products by their sales or may be top 5 students by their marks. Also, while working with Databases, we might need to identify the duplicates on the basis of combination of columns. These are the situations where the Window Functions are of great use and this is your guide in understanding them through examples in MySQL.

We can use a Window Function in our query like below:
Select *, WinFn() over(PARTITION BY col1 ORDER BY col2) from table
Here the clause over() creates a window for the function. This window is created on basis of the column specified after the clause PARTITION BY. So col1 in this case and ordering of records under that partition is done by the column specified after the ORDER BY clause. So col2 in this case. The ORDER BY clause is mandatory while the PARTITION BY clause is optional. If PARTITION BY clause is skipped whole table/dataset is considered to be one window. Let’s understand this through an example.

Consider a students table show below:

As seen above the table contains the names, departments and marks of students studying in an University. The marks indicate the score of the students in their first year University exam, the papers for which were same across the departments.

Now say you want to order your students on basis of their marks. This can be done in three ways:

i) Every student gets a different order.
This can be achieved through the window function called ROW_NUMBER() by using the below query:

select 
  student_id,
  firstname,
  surname,
  dept,
  marks,
  ROW_NUMBER() OVER(ORDER BY marks DESC,/*list tiebreak params here*/) as ROW_NUM 
from students;

Output:

As seen above, every student is alloted an unique order. For cases where two students have same marks we use other parameters like dept or student_id for tie break and in case if we don’t explicitly mention tie breaking parameters then the MySQL DB engine itself does tie break internally and assigns unique ROW_NUM to every student.

ii) Students with the same marks receive the same order, but the student with next highest marks may have a gap in the ordering.
This can be achieved through the window function called RANK() by using the below query:

select 
  student_id,
  firstname,
  surname,
  dept,
  marks,
  RANK() OVER(ORDER BY marks DESC) as RNK
from students;

Output:

In the above image you can see there are three students with the rank 3 and the rank assigned to the next highest scorer is 6. Meaning the rank that will be assigned to a scorer would be (rank of previous scorer + no of students with previous score).

Get Mihirsheth’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

iii) Every student with the same marks gets the same order and no gap in the ordering.
This can be achieved through the window function called DENSE_RANK() by using the below query:

select 
  student_id,
  firstname,
  surname,
  dept,
  marks,
  DENSE_RANK() OVER(ORDER BY marks DESC) as DENSE_RNK
from students;

Output:

As we can see in above output that there are three students with 96 marks and all of them have been assigned an order of 3. Also, two students with the next highest score (95) are assigned an order of 4.

Till now we haven’t use the PARTITION BY clause and hence whole table students was considered one window and thereafter the ordering of records were done in three different ways by three different Window Functions. Now, let’s use the PARTITION BY clause and understand what difference it makes.

Let’s assume the students have completed their first year and gave exam for the second year where in the exam papers varied across dept. Co-incidentally all the students scored the same marks (yep they did) as they scored in their first year. So the column marks in the table students remains exactly the same. Now the ordering of the student’s marks at an University level doesn’t make much sense and hence we will need to order them at their Department level. This is where the PARTITION BY clause comes into the picture.

select 
  student_id,
  firstname,
  surname,
  dept,
  marks,
  ROW_NUMBER() OVER(PARTITION BY dept ORDER BY marks DESC) as ROW_NUM 
from students;

The above query will create two partitions (windows) and the window functions will operate on them independently as show below. The student records enclose within a rectangle is one partition (dept=Computer) and the student records outside rectangle is the other partition (dept=Electronics).

With PARTITION BY clause, the logic of ordering for all the three window functions would be exactly the same as explained earlier without a PARTITION BY clause, the only difference would be that now there would be mutiple windows rather than just a single window.

Below are the outputs for using PARTITION BY dept with DENSE_RANK() and RANK():