I am writing this article to share my knowledge about Windows Functions or Windows Ranking Function (It is a subpart of the Windows Functions). There are many Windows functions introduced in SQL Server, such as Rank, Row_Number, Dense_Rank, Lead, Lag, First, Last, Ntile etc. which can be used to work upon a row data set. A row data set means grouping of rows according to certain conditions; or if we talk about the context in this article, we can simply call it a window.
These functions can be associated with the OVER keyword to establish a relationship inside a data set based on the ordering or partition conditions provided to OVER keyword as Over clause accepts two other clauses - Partition By and Order By - to create a window and get the final result set. Along with the functions above, you can use aggregated functions, such as Sum and Avg as well with the OVER clause.
I hope this brief information is enough to go on. In case you find it difficult to understand, I kindly request you to go through the examples in this article and then try and match the words with the examples to have a better understanding. Otherwise, please ping me; I will be happy to help.
So, let’s get started. First of all, we will create a table.
Insert the data with some random data. Here we go.
Now, our table looks like this.
Everything is ready now, so we will use some Windows Functions on this data and see how they give us the result set. I will explain the output as well. Below is the basic query to be used with this given data.
We will see the result first, and then have an understanding of how the function works. When we run this T-SQL, we will get the following output.
Now, let’s understand how every function worked here and what is their use.
- ROW_NUMBER()
As the name suggests it will give us the row numbers. This function can be used to find out the duplicate data when used with the Partition clause. If you see, the first column gives us the row number for every window or row data set, which was partitioned by using PARTITION BY clause inside OVER Clause. What it does is, it breaks the data inside a table into different result set based on the same names. The second group has 2 entries of name B. So, the Row_Number() gives the row number inside that partition, giving us the option to filter the duplicates where Row_Number is greater than 1 for a specific data. Below is the query to find the duplicates, in case you need it.
- RANK()
The Rank function gives the ranking to the result set based on condition. If there are the same values, it will give the same rank, and then the next rank would be the next row number. As seen in the example, look at the T-SQL Select Rank() Over(Order By Name), A is given 1, then B is given 2, then C is given 4 (Instead of 3, it is given 4). If you see the Select Rank() Over(Partition By Name Order By Name), you will see that it gives 1 for every record, it is because inside a partition also it finds the same value and starts the ranking inside the partition from 1.
- DENSE_RANK()
It performs the same as Rank(), just with a difference that it does not skip the numbers in between, it gives one rank to the same values and the next rank would be the next value in the series. Here C is given rank 3 using DENSE_RANK() function, opposed to 4 given by RANK() function.
- NTILE()
Ntile groups a result set based on the value given as a parameter. Here if you see there are 10 records, now we defined Ntile as NTILE(2), that means we want to group the records into 2 Groups. And if you see the result it will show you the same thing. The result set is given rank based on the division of the group.
I hope this gives you a better understanding about the Windows Ranking Functions. Here, the PARTITION BY NAME is creating a Row Set or you can say a WINDOW, to be operated upon by the functions such as Rank, Dense_Rank, Ntile, Row_Number. This is all from my side regarding Windows Ranking Functions. I hope this will help you in a way. In the coming articles, I will be discussing some more Windows Functions such as Lead, Lag, First_Value, Last_Value.
Any feedback will be welcomed and I will try to improve upon it. May you all have a blessed time ahead. Happy Learning!