Thursday, October 29, 2015

Rank and Dense Rank in SQL Server

CREATE TABLE [dbo].[Employees1]
(
[Id] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[Dept] [nvarchar](50) NULL,
[Gender] [nvarchar](50) NULL,
[Country] [nvarchar](50) NULL,
[Salary] [float] NULL,
)

Insert into Employees Values(1,'John','IT','Male','UK',5000)
Insert into Employees Values(2,'Mary','HR','Female','India',3000)
Insert into Employees Values(3,'Todd','IT','Male','UK',3500)
Insert into Employees Values(4,'Pam','HR','Female','India',4000)
Insert into Employees Values(5,'Tatcher','Payroll','Male','USA',2000)
Insert into Employees Values(6,'Sunil','IT','Male','USA',1400)
Insert into Employees Values(7,'Hari','Payroll','Male','UK',2500)
Insert into Employees Values(8,'Sunitha','HR','Female','India',4000)
Insert into Employees Values(9,'Sneha','IT','Female','India',3000)
Insert into Employees Values(10,'Ruby','Payroll','Male','UK',4600)

With Result AS
(
Select Salary, RANK() Over (Order by Salary Desc) as SalaryRank From employees
)
Select Top 1 Salary from Result Where SalaryRank = 2


With Result AS
(
Select Salary, DENSE_RANK() Over (Order by Salary Desc) as SalaryRank From employees
)
Select Top 1 Salary from Result Where SalaryRank = 2


With Result AS
(
Select Gender, Salary, DENSE_RANK() Over (Pratition by Gender Order by Salary Desc) as SalaryRank From employees
)
Select Top 1 Salary from Result Where SalaryRank = 3 and Gender ='Female'