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

User · Answer

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.

User · Answer

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  gt  Emp1 Salary

User · Answer

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

User · Answer

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

User · Answer

for oracle it goes like this   select salary from employee where rownnum lt  3 order by salary desc minus select salary from employee where rownnum lt  2 order by salary desc

User · Answer

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

User · Answer

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

User · Answer

SELECT Max salary   FROM   employee  WHERE  salary  lt   SELECT Max salary                    FROM   employee                   WHERE  salary NOT IN SELECT Max salary                                         FROM   employee      hope this helped you

User · Answer

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  lt   select max salary  from users where salary  lt   select max salary  from users    order by salary desc limit 1

User · Answer

You can get the third highest salary by using limit   by using TOP keyword and sub-query   TOP keyword  SELECT TOP 1 salary  FROM       SELECT TOP 3 salary       FROM Table Name       ORDER BY salary DESC  AS Comp  ORDER BY salary ASC  limit  SELECT salary  FROM Table Name  ORDER BY salary DESC  LIMIT 2  1  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

User · Answer

WITH CTE AS       SELECT Salary             RN   ROW NUMBER   OVER  ORDER BY Salary DESC      FROM Employee   SELECT salary FROM CTE WHERE RN   3

User · Answer

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

User · Answer

SELECT   FROM employee ORDER BY salary DESC LIMIT 1 OFFSET 2

User · Answer

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

User · Answer

SELECT DISTINCT MAX salary  AS max FROM STAFF WHERE salary IN  SELECT salary   FROM STAFF  WHERE salary lt  SELECT MAX salary  AS maxima   FROM STAFF  WHERE salary lt    SELECT MAX salary  AS maxima   FROM STAFF   GROUP BY salary     I have tried other ways they are not right  This one works

User · Answer

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

User · Answer

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

User · Answer

SELECT id FROM tablename ORDER BY id DESC LIMIT 2   1  This is only for get 3rd highest value

User · Answer

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  amp NUM

User · Answer

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

User · Answer

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

User · Answer

The SQL-Server implementation of this will be   SELECT SALARY FROM EMPLOYEES OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY

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

Examples related to sql