Friday 23 November 2012

DENSE_RANK(), RANK() and ROW_NUMBER()

CREATE TABLE #Department(DeptId int NOT NULL,DeptName nvarchar(max))
CREATE TABLE #Emp(EmpId int NOT NULL,DeptId int NOT NULL,EmpName nvarchar(max),Salary int)

INSERT INTO #Department (DeptId,DeptName) values(1,'D1')
INSERT INTO #Department (DeptId,DeptName) values(2,'D2')

INSERT INTO #Emp (EmpID,DeptId,EmpName,Salary) values(1,1,'A1',1000)
INSERT INTO #Emp (EmpID,DeptId,EmpName,Salary) values(2,1,'A2',2000)
INSERT INTO #Emp (EmpID,DeptId,EmpName,Salary) values(3,1,'A3',1000)
INSERT INTO #Emp (EmpID,DeptId,EmpName,Salary) values(4,2,'A4',1000)
INSERT INTO #Emp (EmpID,DeptId,EmpName,Salary) values(5,2,'A5',5000)

select dense_rank() over (partition by DeptId order by Salary) [dense_rank],
             rank() over (partition by DeptId order by Salary) [rank],
       row_number() over (partition by DeptId order by Salary) [row_number],
              DeptId,Salary
from #Emp;

Drop Table #Emp
Drop Table #Department
Output of above code: