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

Refer following query for getting nth highest salary. By this way you get nth highest salary. If you want get nth lowest salary only you need to replace DESC by ASC in the query.

Answer 2

You've tagged your question Oracle so you could just use the NTH_VALUE() function... it's an analytic function unfortunately but your query would be simplified to:

select distinct nth_value(salary, 3) over ()
  from employees 

SQL Fiddle

From 12c Oracle finally catches up with the rest of the world and includes OFFSET so you could use this instead:

select salary
  from employees
 order by salary
offset n - 1
 fetch next row only

Answer 3

you can use something like this.. this is what i have tested and then pasted here

SELECT *
FROM   tblname
WHERE  salary = (SELECT *
                 FROM   (SELECT *
                         FROM   (SELECT *
                                 FROM   (SELECT DISTINCT( salary )
                                         FROM   tblname
                                         ORDER  BY salary DESC) A
                                 WHERE  rownum <= nth) B
                         ORDER  BY salary ASC) C
                 WHERE  rownum <= 1) 

in place of 'tblname' give your table name and then in place nth give your desired nth highest salary that you want

you can see in the screen shot that it is working.

Answer 4

select * 
  from ( select s.*, rank() over (order by salary desc) as rownumber
           from salary )
 where rownumber = nth

pass your salary number in place of "nth"

Answer 5

We can do this by correlated subquery.

SELECT Salary
FROM Employee E1
WHERE N-1 = (SELECT COUNT(*)
         FROM Employee E2
         WHERE E1.salary <E2.Salary) 

For further knowledge please check this link.. Correlated Subquery with example

Answer 6

You can find plenty of stuff on google

select * 
  from table_name T1 
 where Nth = (select count(distinct (T2.sal)) 
                from table_name T2 
               where T1.sal <= T2.sal )