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'
(
[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)
(
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'