SQL query to find third highest salary in company

Question

I need to write a query that will return the third highest salaried employee in the company.

I was trying to accomplish this with subqueries, but could not get the answer. My attempts are below:

select Max(salary)
from employees
where Salary not in

 (select Max(salary)
from employees
where Salary not in 

(select Max(salary)
from employees));

My thought was that I could use 2 subqueries to elimitate the first and second highest salaries. Then I could simply select the MAX() salary that is remaining. Is this a good option, or is there a better way to achieve this?

Answer 1

The most simple way that should work in any database is to do following:

SELECT * FROM `employee` ORDER BY `salary` DESC LIMIT 1 OFFSET 2;

Which orders employees by salary and then tells db to return a single result (1 in LIMIT) counting from third row in result set (2 in OFFSET). It may be OFFSET 3 if your DB counts result rows from 1 and not from 0.

This example should work in MySQL and PostgreSQL.

Answer 2

You can get the third highest salary by using limit , by using TOP keyword and sub-query

1. TOP keyword

   SELECT TOP 1 salary 
   FROM 
       (SELECT TOP 3 salary 
        FROM Table_Name 
        ORDER BY salary DESC) AS Comp 
   ORDER BY salary ASC
   

2. limit

   SELECT salary 
   FROM Table_Name 
   ORDER BY salary DESC 
   LIMIT 2, 1
   

3. by subquery

   SELECT salary  
   FROM 
       (SELECT salary 
        FROM Table_Name 
        ORDER BY salary DESC 
        LIMIT 3) AS Comp 
   ORDER BY salary 
   LIMIT 1;
   

I think anyone of these help you.