SQL query to find Nth highest salary from a salary

Question

How can I find the Nth highest salary in a table containing salaries in SQL Server?

Answer 1

Dont forget to use the distinct keyword:-

SELECT TOP 1 Salary
FROM 
(
    SELECT Distinct TOP N Salary
    FROM Salaries
    ORDER BY Salary DESC
) SalarySubquery
ORDER BY Salary ASC

Answer 2

try it...

use table_name
select MAX(salary)
from emp_salary
WHERE marks NOT IN (select MAX(marks)
from student_marks )

Answer 3

You could use row_number to pick a specific row. For example, the 42nd highest salary:

select  *
from    (
        select  row_number() over (order by Salary desc) as rn
        ,       *
        from    YourTable
        ) as Subquery
where   rn = 42

Windowed functions like row_number can only appear in select or order by clauses. The workaround is placing the row_number in a subquery.

Answer 4

Solution 1: This SQL to find the Nth highest salary should work in SQL Server, MySQL, DB2, Oracle, Teradata, and almost any other RDBMS: (note: low performance because of subquery)

SELECT * /*This is the outer query part */
FROM Employee Emp1
WHERE (N-1) = ( /* Subquery starts here */
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)

The most important thing to understand in the query above is that the subquery is evaluated each and every time a row is processed by the outer query. In other words, the inner query can not be processed independently of the outer query since the inner query uses the Emp1 value as well.

In order to find the Nth highest salary, we just find the salary that has exactly N-1 salaries greater than itself.

---

Solution 2: Find the nth highest salary using the TOP keyword in SQL Server

SELECT TOP 1 Salary
FROM (
      SELECT DISTINCT TOP N Salary
      FROM Employee
      ORDER BY Salary DESC
      ) AS Emp
ORDER BY Salary

---

Solution 3: Find the nth highest salary in SQL Server without using TOP

SELECT Salary FROM Employee 
ORDER BY Salary DESC OFFSET N-1 ROW(S) 
FETCH FIRST ROW ONLY

Note that I haven’t personally tested the SQL above, and I believe that it will only work in SQL Server 2012 and up.

Answer 5

Simple way WITHOUT using any special feature specific to Oracle, MySQL etc. Suppose in EMPLOYEE table Salaries can be repeated. Use query to find out rank of each ID.

select  *
from  (
select tout.sal, id, (select count(*) +1 from (select distinct(sal) distsal from     
EMPLOYEE ) where  distsal >tout.sal)  as rank  from EMPLOYEE tout
) result
order by rank

First we find out distinct salaries. Then we find out count of distinct salaries greater than each row. This is nothing but the rank of that id. For highest salary, this count will be zero. So '+1' is done to start rank from 1.

Now we can get IDs at Nth rank by adding where clause to above query.

select  *
from  (
select tout.sal, id, (select count(*) +1 from (select distinct(sal) distsal from     
EMPLOYEE ) where  distsal >tout.sal)  as rank  from EMPLOYEE tout
) result
where rank = N;