Difference Between Row_Number() Rank() And Dense_Rank() In SQL Server
Ans:
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 than allow the over clause in SQL standard.
Partition
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
Rank():
This function will assign a unique value to each distinct Row, but it leaves a gap between the groups.
Example:
SELECT EmpName, EmpSalary ,rank() over(order by EmpName) as rankID from Employe
Dense Rank():
Dense_Rank() Funcation is similar to Rank with only difference, this will not leave gaps between groups.
Example:
Ans:
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 than allow the over clause in SQL standard.
Partition
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
Rank():
This function will assign a unique value to each distinct Row, but it leaves a gap between the groups.
Example:
SELECT EmpName, EmpSalary ,rank() over(order by EmpName) as rankID from Employe
Dense Rank():
Dense_Rank() Funcation 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
No comments:
Post a Comment