Friday, 2 March 2018

Sql query for getting sixth highest salary if salary is duplicated and with Id column

create table #tempEmp
(
  Id int identity(1,1),
  Salary decimal(18,2)
)

insert into #tempEmp(Salary) values(1000),(500),(200),(400),(3000),(2000),(1500),(5000),(1000),(500)


select Max(t.Id),
       Max(t.Salary) as Salary 
   from #tempEmp t 
   where 6 = (
               select 
               count(distinct t1.salary) 
       from #tempEmp t1 
               where t1.Salary >= t.Salary
)

No comments:

Post a Comment