Difference between ROW_NUMBER(),RANK() and DENSE_RANK() function in SQL


Create table #tamp
(
EmpID  int identity(1,1) primary key(EmpID),
Name varchar(200),
Salary int
)


select * from #tamp

--insert into #tamp values('Rahul',10000)
--insert into #tamp values('Vishal',15000)
--insert into #tamp values('Mehul',20000)

--insert into #tamp values('Hiren',15000)
--insert into #tamp values('Amit',15000)
--insert into #tamp values('Mihir',20000)

 ROW_NUMBER() Function keeps increasing integer by one and it is not caring duplicate values
RANK() and DENSE_RANK() functions are looking for duplicate values
RANK() Function The integer value is increasing by one but if the same value (Salary) is present in the table, then the same integer value is given to all the rows having the same value(Salary)

Select * ,
RANK() over (order by salary desc) as rank,
DENSE_RANK() over (order by salary desc) as denserank,
ROW_NUMBER() over (order by salary desc) as rownumber
from #tamp

In RANK function, the next row after the duplicate values (salary), will not give the integer value as next rank but instead of it, it skips those ranks and gives what is the next incremented rank. In the above case, the first three values are having same salary so it gives same rank to them but in next row, it gives as 4, It skips two and three as first three rows have same ranks.

In DENSE_RANK function, it will not skip any rank. This means the next row after the duplicate value (salary) rows will have the next rank in the sequence


Comments

Post a Comment

Popular posts from this blog

Stack vs Heap So far we have seen how to declare basic type variables such as int , double , etc, and complex types such as arrays and structs. The way we have been declaring them so far, with a syntax that is like other languages such as MATLAB, Python, etc, puts these variables on the stack in C. The Stack What is the stack? It's a special region of your computer's memory that stores temporary variables created by each function (including the main() function). The stack is a "FILO" (first in, last out) data structure, that is managed and optimized by the CPU quite closely. Every time a function declares a new variable, it is "pushed" onto the stack. Then every time a function exits, all of the variables pushed onto the stack by that function, are freed (that is to say, they are deleted). Once a stack variable is freed, that region of memory becomes available for other stack variables. The advantage of using the stack to store variable...
Read more

Validate Mobile Number with 10 Digits in ASP.Net

Here is the code for validating textbox for 10 digit mobile number. .ASPX File: <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br\>     <asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server"        ControlToValidate="TextBox1" ErrorMessage="RegularExpressionValidator"      ValidationExpression="[0-9]{10}"></asp:RegularExpressionValidator>
Read more
What is  Abstraction? Abstraction is another good feature of OOPS. Abstraction means to show only the necessary details to the client of the object . Do you know the inner details of the Monitor of your PC? What happen when you switch ON Monitor? Does this matter to you what is happening inside the Monitor? No Right, Important thing for you is weather Monitor is ON or NOT. When you change the gear of your vehicle are you really concern about the inner details of your vehicle engine? No but what matter to you is that Gear must get changed that’s it!! This is abstraction; show only the details which matter to the user.  Let’s say you have a method "CalculateSalary" in your Employee class, which takes EmployeeId as parameter and returns the salary of the employee for the current month as an integer value. Now if someone wants to use that method. He does not need to care about how Employee object calculates the salary? An only thing he needs to be concern is name...
Read more