Nth max salary in Oracle

To find out the Nth max sal in oracle i'm using below query

SELECT DISTINCE sal
FROM emp a
WHERE (
SELECT COUNT(DISTINCE sal)
FROM emp b
WHERE a.sal<=b.sal)=&n;


But According to me by using the above query it will take more time to execute if table size is big.

i'm trying to use the below query

SELECT sal
FROM (
SELECT DISTINCE sal
FROM emp
ORDER BY sal DESC )
WHERE rownum=3;


but not getting output.. any suggetions please .. Please share any link on how to optimise queries and decrease the time for a query to execute.

what's this DISTINCE sal or is it DISTINCT ??
– TeamDataViz.
Nov 20 '13 at 9:09

DISTINCE

possible duplicate of Find out the nth-highest salary from table
– bummi
Nov 28 '14 at 18:27

22 Answers
22

try this

select *
from
(
select
sal
,dense_rank() over (order by sal desc) ranking
from table
)
where ranking = 4 -- Replace 4 with any value of N


if two persons have same salary they must given same rank,so dense_rank must be used in this case
– TeamDataViz.
Nov 20 '13 at 9:02

dense_rank

can we use rank() in place of that?
– Sai
Nov 20 '13 at 9:21

The DENSE_RANK function acts like the RANK function except that it assigns consecutive ranks.see here
– TeamDataViz.
Nov 20 '13 at 9:24

@SaiDattu check out here for cpu_time
– TeamDataViz.
Nov 20 '13 at 9:27

Thank u soo much vijay..
– Sai
Nov 20 '13 at 9:36

SELECT sal FROM (
SELECT sal, row_number() OVER (order by sal desc) AS rn FROM emp
)
WHERE rn = 3


Yes, it will take longer to execute if the table is big. But for "N-th row" queries the only way is to look through all the data and sort it. It will be definitely much faster if you have an index on sal.

You need to use desc on order by, And as OP need the distinct salary , a group by salary would be useful for non repeating ranked salary.
– ajmalmhd04
Nov 20 '13 at 8:55

desc

It's important to note the differences between row_number, rank, and dense_rank for this specific requirement. What should happen when you have more then one person with the same salary ? should you count them in the same position ... ?
– haki
Nov 20 '13 at 9:00

row_number

rank

dense_rank

@Kombajn zbożowy here if the salaries are like 5000,3000,5000,3000,2000 then will it generate wrong output??
– Sai
Nov 20 '13 at 9:07

Well, it depends on the exact behaviour you need. For salaries: 5000,3000,5000,3000,2000 I would say the answer is 3000 (and my query works like this). But you might want 2000 (3rd unique salary), in this case right, you would need to rebuild the query.
– Kombajn zbożowy
Nov 20 '13 at 9:52

Right, should have been order by sal desc, that's just a typo, edited.
– Kombajn zbożowy
Nov 20 '13 at 9:53

SELECT *
FROM Employee Emp1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)


We could write as below mentioned also.

select min(sal) from (select sal from emp where rownum=<&n order by sal desc);

select min(sal) from (select sal from emp where rownum=<&n order by sal desc);

This will show the 3rd max salary from table employee.
If you want to find out the 5th or 6th (whatever you want) value then just change the where condition like this where rownum<=5" or "where rownum<=6 and so on...

where rownum<=5" or "where rownum<=6

select min(sal) from(select distinct(sal) from emp where rownum<=3 order by sal desc);


SELECT sal
FROM (
SELECT empno,
deptno, sal,
dense_rank( ) over ( partition by deptno order by sal desc) NRANK
FROM emp
)
WHERE NRANK = 4


by executing above we will get deparment wise highest salary.. i have asked highest sal among all the dept's.
– Sai
Dec 10 '13 at 11:35

you need to remove partition by in that case, please find code below
– Anant_00
Dec 11 '13 at 5:54

SELECT *
FROM (
SELECT empno,
deptno, sal,
dense_rank( ) over ( order by sal desc) NRANK
FROM emp
)
WHERE NRANK = 4


you can replace the 2 with your desired number

select * from ( select distinct (sal),ROW_NUMBER() OVER (order by sal desc) rn from emp ) where rn=2


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.

LIMIT Only work in MYSQL
– Asanka Lakmal
Dec 2 '15 at 6:17

Yes. It Works in MySQL.
– Vijay Bhatt
Dec 3 '15 at 8:53

Now you try this you will get for sure:

SELECT DISTINCT sal
FROM emp a
WHERE (
SELECT COUNT(DISTINCT sal)
FROM emp b
WHERE a.sal<=b.sal)=&n;


For your information, if you want the nth least sal:

SELECT DISTINCT sal
FROM emp a
WHERE (
SELECT COUNT(DISTINCT sal)
FROM emp b
WHERE a.sal>=b.sal)=&n;


select min(sal) from (select distinct sal from employee order by sal DESC) where rownum<=N;

place the number whatever the highest sal you want to retrieve.

It works in Oracle. I don't know about the remaining databases
– mahesh
Aug 5 '16 at 16:19

Try out following:

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!

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


dense_rank() is used for the salary has to be same.So it give the proper output instead of using rank().

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


This is for 10th max salary, you can replace 10 with n.

These queries will also work:

Workaround 1)

SELECT ename, sal
FROM Emp e1 WHERE n-1 = (SELECT COUNT(DISTINCT sal)
FROM Emp e2 WHERE e2.sal > e1.sal)


Workaround 2) using row_num function.

SELECT *
FROM (
SELECT e.*, ROW_NUMBER() OVER (ORDER BY sal DESC) rn FROM Emp e
) WHERE rn = n;


Workaround 3 ) using rownum pseudocolumn

Select MAX(SAL)
from (
Select *
from (
Select *
from EMP
order by SAL Desc
) where rownum <= n
)


This will also work :

with data as
(
select sal,rwid from (
select salary as sal,rowid as rwid from salary order by salary desc
)
where rownum < 5
)
select * from salary a
where rowid = (select min(rwid) from data)


select min(sal) from (select distinct(sal) from emp order by sal desc) where rownum <=&n;


Inner query select distinct(sal) from emp order by sal desc will give the below output as given below.

select distinct(sal) from emp order by sal desc

SAL
5000
3000
2975
2850
2450
1600
1500
1300
1250
1100
950
800

without distinct in the above query select sal from emp order by sal desc
output as given below.

select sal from emp order by sal desc

SAL
5000
3000
3000
2975
2850
2450
1600
1500
1300
1250
1250
1100
950
800

outer query will give the 'N'th max sal (E.g) I have tried here for 4th Max sal and out put as given below.

MIN(SAL)
2850

Select min(salary) from (
select distinct(salary) from empdetails order by salary desc
) where rownum <=&rn


Just enter nth number which you want.

Try this:

SELECT min(sal) FROM (
SELECT sal FROM emp ORDER BY sal desc) WHERE ROWNUM <= 3; -- Replace 3 with any value of N


You can optimize the query using Dense_rank() function.

for Example :

select distinct salary from
( select salary ,dense_rank() over (order by salary desc) ranking
from Employee
)
where ranking = 6

Note: ranking 6 is the number of nth order.

Please include solution not only description Refer :stackoverflow.com/help/how-to-answer
– Dinesh Ghule
Jun 29 at 9:16

5th highest salary:

SELECT
*
FROM
emp a
WHERE
4 = (
SELECT
COUNT(DISTINCT b.sal)
FROM
emp b
WHERE
a.sal < b.sal
)


Replace 4 with any value of N.

It would be helpful to give an answer that contains full code (correctly formatted) and an explanation why your answer provides a solution. What is to learn from this?
– Timusan
Jul 6 '15 at 6:24

There are three methods are there...

SELECT salary,first_name,rnk
FROM (SELECT salary,first_name,rank() over (order by salary desc nulls last) as rnk from emp) where rnk=3;


SELECT salary,first_name,rnk
FROM (SELECT salary,first_name,dense_rank() over (order by salary desc nulls last) as rnk from emp) where rnk=3;


select rnk,first_name,salary
from (select rownum as rnk ,first_name,salary
from (select first_name,salary
from emp order by salary desc nulls last)) where rnk=3


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.