Alex Rivera | Logout

How to get second-highest salary employees in a table

Asked 2011-09-14T13:48:58.323
46

It's a question I got this afternoon:

There a table contains ID, Name, and Salary of Employees, get names of the second-highest salary employees, in SQL Server

Here's my answer, I just wrote it in paper and not sure that it's perfectly valid, but it seems to work: 

SELECT Name FROM Employees WHERE Salary = 
( SELECT DISTINCT TOP (1) Salary FROM Employees WHERE Salary NOT IN
 (SELECT DISTINCT TOP (1) Salary FROM Employees ORDER BY Salary DESCENDING)
ORDER BY Salary DESCENDING)

I think it's ugly, but it's the only solution come to my mind.

Can you suggest me a better query?

Thank you very much.

sql sql-server t-sql optimization
Edit
Report

2 Answers

37
SELECT * from Employee 
WHERE Salary IN (SELECT MAX(Salary) 
                 FROM Employee 
                 WHERE Salary NOT IN (SELECT MAX(Salary) 
                                      FFROM employee));

Try like this..

answered 2012-08-08T20:19:34.270
4

Simple way WITHOUT using any special feature specific to Oracle, MySQL etc.

Suppose EMPLOYEE table has data as below. Salaries can be repeated. enter image description here

By manual analysis we can decide ranks as follows :-
enter image description here

Same result can be achieved by query

select  *
from  (
select tout.sal, id, (select count(*) +1 from (select distinct(sal) distsal from     
EMPLOYEE ) where  distsal >tout.sal)  as rank  from EMPLOYEE tout
) result
order by rank

enter image description here

First we find out distinct salaries. Then we find out count of distinct salaries greater than each row. This is nothing but the rank of that id. For highest salary, this count will be zero. So '+1' is done to start rank from 1.

Now we can get IDs at Nth rank by adding where clause to above query.

select  *
from  (
select tout.sal, id, (select count(*) +1 from (select distinct(sal) distsal from     
EMPLOYEE ) where  distsal >tout.sal)  as rank  from EMPLOYEE tout
) result
where rank = N;
answered 2012-11-16T05:22:17.310

Your Answer