Selecting the record(s) with the “second” highest something

I have written the following SQL commands for retrieving data from a table called Employee.

I was able to get the highest/maximum salary as well as the second highest/maximum salary, but I am having difficulty writing the same when the whole record needs to be returned.

Select all the employees.

SELECT * FROM Employee

Return the highest salary. This returns the maximum salary amount which is 90000

SELECT MAX(salary) FROM Employee

Return the employee record with the highest salary. This returns all the records of the person/people with their salary being the maximum salary which is 90000 that is only John Henry in this case.

SELECT *
FROM Employee
WHERE salary = ( SELECT MAX(salary) FROM Employee )

Return every other employee record; i.e. everyone except the one with the highest salary.

SELECT *
FROM Employee
WHERE salary != ( SELECT MAX(salary) FROM Employee )

Return the second highest salary. This returns the second maximum salary amount which is 85000

SELECT MAX(salary) FROM Employee
WHERE salary != ( SELECT MAX(salary) FROM Employee )

Return the employee record with the second highest salary. This returns all the records of the person/people with their salary being the second maximum salary which is 85000 that is only Michael Greenback in this case.

I am stuck in this… I tried using HAVING as an extra condition, but however I arrange it to specify a condition, I get syntax errors. How do I do this?

Answer

Window functions are the built-in functionality to do this. In particular, dense_rank():

select e.*
from (select e.*, dense_rank() over (order by salary desc) as seqnum
      from employee e
     ) e
where seqnum = 2;

Advertisement

Answer