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
Friday 23 November 2012
DENSE_RANK(), RANK() and ROW_NUMBER()
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment