Introduction
In this article we will learn about some SQL functions Row_Number() ,Rank(), and Dense_Rank() and the difference between them.
Creating a table in SQL Server
Here I have an Employe table, the following is the sample data for the Employe Table.
ROW_NUMBER() Function without Partition By clause
Row_number plays a very important role in SQL server. Row_Number function can help to perform more complex ordering of row in the report format that allow the over clause in SQL standard.
Syntax
ROW_NUMBER () OVER ([PARTITION BY value_exp, ... [ n ]] ORDER BY_clause)
Here we will play with the above Employe table. Let's see how we can use Row_Number() function.
Over specified the order of the row and Order by sort order for the record. By default order by sort in ascending order.
Example
- select *, ROW_NUMBER() over(order by EmpName) as rownumber from Employe
The following is the output of the above query.
ROW_NUMBER() Function with Partition By clause
If we want to add row number to each group, and it is reset for every group, let's take a look at the Employe table
Example
- Select *, ROW_NUMBER() over(partition by Empname order by Empname ) as rownumber from Employe
The following is the OUTPUT of the above query
Rank() Function in SQL Server
This function will assign a unique value to each distinct Row, but it leaves a group between the groups.
Example
- SELECT EmpName, EmpSalary ,rank() over(order by EmpName) as rankID from Employe
The following is the OUTPUT of the above query.
Gap represents number of occurrence example - EmpName="atul" is repeated 3 times and has rank "1", the next rank will be 1+3=4 and same with the next value.
Dense_Rank() Function in SQL Server
Dense_Rank() Function is similar to Rank with only difference, this will not leave gaps between groups.
Example
- SELECT EmpName ,EmpSalary ,DENSE_RANK() over(order by EmpName) as DrankID from Employe
The following is the OUTPUT of the above query:
Summary
In this article we learned Row_Number() ,Rank(), and Dense_Rank() in SQL Server.