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

DECLARE M INT; SET M=N-1; SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT M,1;

Answer 2

General query for all Database

SELECT DISTINCT salary FROM emp X WHERE n = 
    ( SELECT COUNT(DISTINCT salary) FROM emp WHERE salary >=X.salary )

Replace n with the given number. for example to get the 3rd highest salary

SELECT DISTINCT salary FROM emp X WHERE 3 = 
( SELECT COUNT(DISTINCT salary) FROM emp WHERE salary >=X.salary )

OR

in any programming language

select * from emp order by salary

then iterate the result set in programming language (JAVA, .net or php)

Mysql

SELECT DISTINCT salary FROM emp X order by salary desc limit n,1

Answer 3

Select n-th highest salary in a simple way

SELECT emp_no, sal
FROM
(
select emp_no, sal, ROW_NUMBER() OVER (order by sal desc) RN 
from emp 
order by sal desc
)
WHERE RN = n;

Where n = the n-th number u want.....

Answer 4

In Sql server 2012 and above. Please Refer this link for Fetch, Offset, Sql server Page

 Use AdventureWorks /* AdventureWorks 2014 DB*/

Select distinct(CommissionPct) from Sales.SalesPerson
order by CommissionPct desc OffSet 3 Rows Fetch next 1 Rows only

--This for 4Th highest value (N-1)

Look here