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

select * from (

select sal, RN=row_Number() over (order by sal DESC ) FROM employee

)s where RN = 3

Answer 2

Mysql Query to find third highest salary in company

select Max(payment) from wallets where payment < (select Max(payment) from wallets where payment < (select Max(payment) from wallets));

Answer 3

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

Answer 4

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.

Edit:

But there's a catch if you only want the 3rd highest DISTINCT salary. Than you should add the DISTINCT keyword.

In case of salary list: 100, 90, 90, 80, 70.
In the above query it will produce the 3rd highest salary which is 90. But if you mean the 3rd distinct which is 80 than you should use

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

But there's a catch, this will return you only 1 column which is Salary, because in order to operate the distinction operation, DISTINCT can only operate on a specific set of columns. This means we should add another wrapping query to extract the employees(There can be multiple) that matches that result. Thus I added LIMIT 1 at the end.

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

Examples can be found HERE

Answer 5

SELECT * FROM(

SELECT salary, DENSE_RANK()

OVER(ORDER BY salary DESC)r FROM Employee)

WHERE r=&n;

To find the 3rd highest salary set n = 3

Answer 6

--Oracle SQL with temp as ( select distinct salary from HR.EMPLOYEES order by SALARY desc ) select min(temp.salary) from temp where rownum <= 3;

Answer 7

SELECT MAX(salary) FROM employees GROUP BY salary ORDER BY salary DESC LIMIT 1 OFFSET 2;

Answer 8

SELECT salary FROM employees e1
       WHERE N-1 = (SELECT COUNT(DISTINCT salary) FROM employees e2
                    WHERE e2.salary > e1.salary)

Here, I have solved it with a correlated nested query. It is a generalized Query so if you want to print 4th, 5th, or any number of highest salary it will work perfectly even if there are any duplicate salaries.

So, what you have to do is simply change the N value here. So, in your case, it will be,

SELECT salary FROM employees e1
       WHERE 3-1 = (SELECT COUNT(DISTINCT salary) FROM employees e2
                    WHERE e2.salary > e1.salary)

Answer 9

SELECT * 
FROM maintable_B7E8K
order by Salary
desc limit 1 offset 2;

Answer 10

You can find Nth highest salary by making use of just one single query which is very simple to understand:-

select salary from employees e1 where N-1=(select count(distinct salary) from employees e2 where e2.salary>e1.salary);

Here Replace "N" with number(1,2,3,4,5...).This query work properly even when where salaries are duplicate. The simple idea behind this query is that the inner subquery count how many salaries are greater then (N-1). When we get the count then the cursor will point to that row which is N and it simply returns the salary present in that row.

Answer 11

This works fine with Oracle db.

select SAL from ( SELECT DISTINCT SAL FROM EMP ORDER BY SAL DESC FETCH FIRST 3 ROWS ONLY ) ORDER BY SAL  ASC FETCH FIRST 1 ROWS ONLY

Answer 12

you can get any order for salary with that:

select * from 
(
select salary,row_Number() over (order by salary DESC ) RN 
FROM employees
)s
where RN = 3

-- put RN equal to any number of orders. --for your question put 3

Answer 13

in Sql Query you can get nth highest salary

select * from( select empname, sal, dense_rank() over(order by sal desc)r from Employee) where r=&n;

To find to the 2nd highest sal set n = 2

To find 3rd highest sal set n = 3 and so on.

Answer 14

This is a MYSQL query.

Explanation: The subquery returns top 3 salaries. From the returned result, we select the minimum salary, which is the 3rd highest salary.

SELECT MIN(Salary)
FROM (
    SELECT Salary
    FROM Employees
    ORDER BY Salary DESC
    LIMIT 3
) AS TopThreeSalary;

Answer 15

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
);

Answer 16

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 

Answer 17

The SQL-Server implementation of this will be:

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

Answer 18

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

Answer 19

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;

Answer 20

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

Answer 21

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

Answer 22

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

Answer 23

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;

Answer 24

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.

Answer 25

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)

Answer 26

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

Answer 27

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

Answer 28

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.

Answer 29

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;

Answer 30

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

This is only for get 3rd highest value .