[sql] SQL query to find third highest salary in company

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?

SELECT id FROM tablename ORDER BY id DESC LIMIT 2 , 1

This is only for get 3rd highest value .

The SQL-Server implementation of this will be:

SELECT SALARY FROM EMPLOYEES OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY

For me this query work fine in Mysql it will return third max salary from table

SELECT salary FROM users ORDER BY salary DESC LIMIT 1 OFFSET 2;

or

SELECT salary FROM users ORDER BY salary DESC LIMIT 2,1;

I found a very good explanation in

http://www.programmerinterview.com/index.php/database-sql/find-nth-highest-salary-sql/

This query should give nth highest salary

SELECT *
FROM Employee Emp1
WHERE (N-1) = (
    SELECT COUNT(DISTINCT(Emp2.Salary))
    FROM Employee Emp2
    WHERE Emp2.Salary > Emp1.Salary)

for oracle it goes like this:

select salary from employee where rownnum<=3 order by salary desc
minus
select salary from employee where rownnum<=2 order by salary desc;

SELECT TOP 1 BILL_AMT Bill_Amt FROM ( SELECT DISTINCT TOP 3 NH_BL_BILL.BILL_AMT FROM NH_BL_BILL ORDER BY BILL_AMT DESC) A 
ORDER BY BILL_AMT ASC

SELECT * FROM employee ORDER BY salary DESC LIMIT 1 OFFSET 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.

You can use nested query to get that, like below one is explained for the third max salary. Every nested salary is giving you the highest one with the filtered where result and at the end it will return you exact 3rd highest salary irrespective of number of records for the same salary.

select * from users where salary < (select max(salary) from users where salary < (select max(salary) from users))  order by salary desc limit 1

Below query will give accurate answer. Follow and give me comments:

select top 1 salary from (
select DISTINCT  top 3 salary from Table(table name) order by salary  ) as comp
order by personid salary 

SELECT Max(salary) 
FROM   employee 
WHERE  salary < (SELECT Max(salary) 
                 FROM   employee 
                 WHERE  salary NOT IN(SELECT Max(salary) 
                                      FROM   employee)) 

hope this helped you

select min (salary) from Employee where Salary in (Select Top 3 Salary from Employee order by Salary desc)

Note that the third highest salary may be the same the the first highest salary so your current approach wouldn't work.

I would do order the employees by salary and apply a LIMIT 3 at the end of the SQL query. You'll then have the top three of highest salaries and, thus, you also have the third highest salary (if there is one, a company may have two employees and then you wouldn't have a third highest salary).

WITH CTE AS ( SELECT Salary, RN = ROW_NUMBER() OVER (ORDER BY Salary DESC) FROM Employee ) SELECT salary FROM CTE WHERE RN = 3

We can find the Top nth Salary with this Query.

WITH EMPCTE AS ( SELECT E.*, DENSE_RANK() OVER(ORDER BY SALARY DESC) AS DENSERANK FROM EMPLOYEES E ) SELECT * FROM EMPCTE WHERE DENSERANK=&NUM

SELECT DISTINCT MAX(salary) AS max
FROM STAFF
WHERE salary IN
(SELECT salary 
 FROM STAFF
 WHERE salary<(SELECT MAX(salary) AS maxima 
 FROM STAFF
 WHERE salary<
 (SELECT MAX(salary) AS maxima
  FROM STAFF))
GROUP BY salary);

I have tried other ways they are not right. This one works.

You may try (if MySQL):

SELECT salary FROM employee ORDER BY salary DESC LIMIT 2, 1;

This query returns one row after skipping two rows.

You may also want to return distinct salary. For example, if you have 20,20,10 and 5 then 5 is the third highest salary. To do so, add DISTINCT to the above query:

SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT 2, 1;

If SQL Server this could work

SELECT TOP (1) * FROM
   (SELECT TOP (3) salary FROM employees ORDER BY salary DESC) T
ORDER BY salary ASC

As for your number of subqueries question goes it depends on your language. Check this for more information

Is there a nesting limit for correlated subqueries in Oracle?

Some DBMS's don't allow you to run several nested queries. Here is a solution that only uses 1 nested query:

SELECT salary
FROM
(
   SELECT salary
   FROM employees
   ORDER BY salary
   LIMIT 3
) as TBL1
ORDER BY salary DESC
LIMIT 1;

It should give you the desired result. It first finds the 3 largest salaries, then selects the smallest of the three (or the third one if they are equal). Here is an SQLFiddle

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.

You may use this for all employee with 3rd highest salary:

SELECT * FROM `employee` WHERE salary = (
   SELECT DISTINCT(`salary`) FROM `employee` ORDER BY `salary` DESC LIMIT 1 OFFSET 2
);

you can find the third most salary with this query:

SELECT min(salary) 
FROM tblEmployee 
WHERE salary IN (SELECT TOP(3) salary 
                 FROM tblEmployee 
                 ORDER BY salary DESC)