Find out the nth-highest salary from table

Question

name   salary
-----   -----
mohan     500
ram      1000
dinesh   5000
hareesh  6000
mallu    7500
manju    7500
praveen 10000
hari    10000

How would I find the nth-highest salary from the aforementioned table using Oracle?

Answer 1

select distinct salary from emp_table order by salary desc limit n-1,1;

Answer 2

Try out following in Oracle:

SELECT *
FROM
  (SELECT rownum AS rn,
    a.*
  FROM
    (WITH DATA AS -- creating dummy data
    ( SELECT 'MOHAN' AS NAME, 200 AS SALARY FROM DUAL
    UNION ALL
    SELECT 'AKSHAY' AS NAME, 500 AS SALARY FROM DUAL
    UNION ALL
    SELECT 'HARI' AS NAME, 300 AS SALARY FROM DUAL
    UNION ALL
    SELECT 'RAM' AS NAME, 400 AS SALARY FROM DUAL
    )
  SELECT D.* FROM DATA D ORDER BY SALARY DESC
    ) A
  )
WHERE rn = 3; -- specify N'th highest here (In this case fetching 3'rd highest)

Cheers!

Answer 3

In MySql, run the below SQL to find nth highest salary:

SELECT distinct(salary), emp_id, name 
       FROM `emp_salary` 
       group by salary 
       order by salary desc limit N-1,1;

e.g Find 3rd highest salary:

SELECT distinct(salary), emp_id, name 
       FROM `emp_salary` 
       group by salary 
       order by salary desc limit 2,1;

e.g Find 3rd lowest salary (make "order by asc"):

SELECT distinct(salary), emp_id, name 
       FROM `emp_salary` 
       group by salary 
       order by salary asc limit 2,1;

Answer 4

SELECT * FROM (SELECT SALARY, DENSE_RANK() OVER ( ORDER BY SALARY DESC) nth_salary FROM EMPLOYEES)WHERE nth_salary = 1;

It pretty much works in Oracle. You can use ROW_NUMBER() function instead DENSE_RANK but it selects only one record or row for the highest salary even if there are two or more employees having the equal salary.

Answer 5

This article talks about this question in depth, and I will quote code from it below: (Note: see the bottom 2 solutions for Oracle)

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.

---

Solution 4: Works in MySQL

SELECT Salary FROM Employee 
ORDER BY Salary DESC LIMIT n-1,1

The LIMIT clause takes two arguments in that query – the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return.

---

Solution 5: Works in Oracle

select * from (
  select Emp.*, 
row_number() over (order by Salary DESC) rownumb 
from Employee Emp
)
where rownumb = n;  /*n is nth highest salary*/

---

Solution 6: Works in Oracle way 2

select * FROM (
select EmployeeID, Salary
,rank() over (order by Salary DESC) ranking
from Employee
)
WHERE ranking = N;

Answer 6

select * from 
(
    select sal, rank() over (order by sal DESC/ASC) rnk 
    from emp
) 
where rnk = 1/2/3/4/5/6/...;