youv youv - 5 months ago 10
SQL Question

Get employee with third highest salary

I need to fetch all details of employee with third highest salary in the most efficient way.

My query:

select(SELECT MIN(Salary)
FROM (SELECT * TOP (3) Salary
FROM Employees
ORDER BY Salary)


Is there any issue in my query.how can i correct my query.Please help.

Answer

You can use Rownumber:Here i partitioned by empid to avoid ties

;With cte
as
(
select *,row_number() over (partition by empid order by salary desc) as rownum
from 
table
)
select * from cte where rownum=3

if you want to use your query:

SELECT MIN(Salary
FROM   (
SELECT  TOP (3) Salary
       FROM   Employees
       ORDER  BY Salary
)b