0

say I have a table of Employees where I maintain the name of the employee in one column and the salary drawn by them in another. what SQL should I write to find the name of the employee drawing the second highest salary???

MSeth
  • 81
  • 7
  • 1
    Take a look here: http://stackoverflow.com/questions/32100/what-is-the-simplest-sql-query-to-find-the-second-largest-value – niculare Mar 07 '13 at 19:01
  • Which RDBMS are you use? – Hamlet Hakobyan Mar 07 '13 at 19:01
  • 1
    See [here](http://stackoverflow.com/questions/4297160/sql-command-for-finding-the-second-highest-salary?rq=1). Pretty simple query, really. – fcm Mar 07 '13 at 19:03
  • @Hamlet: Not using any particular RDBMS. This was an interview question that I was asked. Generally I use MySQL. – MSeth Mar 07 '13 at 19:07

3 Answers3

2

I would use row_number():

select e.*
from (select e.*, row_number() over (order by salary desc) as seqnum
      from employees e
     ) e
where seqnum = 1

Others have mentioned the limit/top/rownum approach:

select e.*
from (select e.*
      from employees e
      order by salary desc
      limit 2
     ) t
order by salary
limit 1

You can do the same thing with offset:

select e.*
from employees e
order by salary desc
limit 2, 1

However, all of these have a problem if you have a tie for the highest salary. The first method is easily fixed by replacing row_number() with dense_rank():

select e.*
from (select e.*, dense_rank() over (order by salary desc) as seqnum
      from employees e
     ) e
where seqnum = 1

And, another method is:

select e.*
from employees e
where e.salary < (select max(salary) from employees)
order by salary desc
limit 1  -- or top 1 or rownum = 1 etc.
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

You could try selecting from "Top 2", sorted ascending, followed by a "Top 1" which should give you the correct row.

Kevin Matlock
  • 99
  • 3
0

You could do something like:

select * from (select * from Employees order by salary desc limit 2) as A order by salary asc limit 1;
SteveP
  • 18,840
  • 9
  • 47
  • 60