Nth max salary in Oracle

Question

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.

possible duplicate of [Find out the nth-highest salary from table](http://stackoverflow.com/questions/18285903/find-out-the-nth-highest-salary-from-table) — bummi, Nov 28 '14 at 18:27

vhadalgi · Accepted Answer · 2013-11-20T09:00:33.850

25

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

edited Nov 20 '13 at 09:00

answered Nov 20 '13 at 08:51

vhadalgi

7,027
6
38
67

2

if two persons have same salary they must given same rank,so `dense_rank` must be used in this case – vhadalgi Nov 20 '13 at 09:02
can we use rank() in place of that? – Sai Nov 20 '13 at 09:21
The DENSE_RANK function acts like the RANK function except that it assigns consecutive ranks.[see here](http://stackoverflow.com/questions/11183572/whats-the-difference-between-rank-and-dense-rank-functions-in-oracle) – vhadalgi Nov 20 '13 at 09:24
@SaiDattu [check out here for cpu_time](http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2920665938600) – vhadalgi Nov 20 '13 at 09:27
one more doubt please... in the above query we can see only sal col in the result set.. if i want to see to see entire columns..any help?? – Sai Nov 20 '13 at 10:10
@vhadalgi Hi, I am getting this error.... Error Code: 1248. Every derived table must have its own alias – CodeWorld Mar 02 '22 at 09:10

Kombajn zbożowy · Answer 2 · 2013-11-20T09:54:06.773

6

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.

edited Nov 20 '13 at 09:54

answered Nov 20 '13 at 08:49

Kombajn zbożowy

8,755
3
28
60

2

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 08:55
2

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 09:00
@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 09: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 09:52
Right, should have been order by sal *desc*, that's just a typo, edited. – Kombajn zbożowy Nov 20 '13 at 09:53

score 5 · Answer 3 · edited Mar 18 '14 at 04:19

5

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

edited Mar 18 '14 at 04:19

Raging Bull

18,593
13
50
55

answered Mar 18 '14 at 04:03

siva

51
1
1

score 2 · Answer 4 · edited Apr 03 '17 at 10:29

2

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...

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

edited Apr 03 '17 at 10:29

Tony Babarino

3,355
4
32
44

answered Apr 03 '17 at 10:23

Hasnain Latif

55
1
10

Correction needed : where clause should be in the outside query.. it should be : select min(sal) from(select distinct(sal) from emp order by sal desc) where rownum<=3; – abhi Jul 03 '18 at 10:52

score 2 · Answer 5 · edited Sep 24 '19 at 07:24

2

SELECT Min(sal)
FROM   (SELECT DISTINCT sal
        FROM   emp
        WHERE  sal IS NOT NULL
        ORDER  BY sal DESC)
WHERE  rownum <= n;

edited Sep 24 '19 at 07:24

Pratham

1,522
1
18
33

answered Sep 24 '19 at 06:48

Sneha

21
1

2

Some explanation is always helpful – Pratham Sep 24 '19 at 07:00

score 1 · Answer 6 · edited Nov 24 '20 at 15:12

1

We could write as below mentioned also.

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

edited Nov 24 '20 at 15:12

Stevoisiak

23,794
27
122
225

answered Aug 08 '16 at 11:19

Yash

634
1
6
17

1

The code will not work as the rownum is not properly applied, rownum gets add after the result. – Ritesh Kumar Aug 01 '19 at 18:28

score 1 · Answer 7 · edited Aug 23 '17 at 09:38

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
)

score 1 · Answer 8 · edited Oct 22 '20 at 12:41

1

The following solution works from 12c onwards:

Select min(sal) from emp where 
Sal in ( select distinct (sal) from emp order by sal desc fetch first n rows only);

Replace n as per your requirement

edited Oct 22 '20 at 12:41

Irina Avram

1,492
2
20
35

answered Oct 22 '20 at 09:41

Sandeep KVS

11
1

score 1 · Answer 9 · answered May 29 '21 at 15:53

1

In my case this Query is successfully executed (Oracle).

select salary from 
(select salary, (dense_rank() 
over (order by salary desc)) R 
from employees)  
where R='10' group by salary;

you can replace value '10' by any value of 'n'.

answered May 29 '21 at 15:53

Sachin Jawale

21
2

score 0 · Answer 10 · answered Dec 10 '13 at 10:30

0

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

answered Dec 10 '13 at 10:30

Anant_00

133
1
5

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 05:54

score 0 · Answer 11 · answered Dec 11 '13 at 05:56

0

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

answered Dec 11 '13 at 05:56

Anant_00

133
1
5

score 0 · Answer 12 · answered Sep 09 '14 at 08:02

0

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

answered Sep 09 '14 at 08:02

Kishore Kumar

909
2
10
15

score 0 · Answer 13 · edited Sep 08 '15 at 06:49

0

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;

edited Sep 08 '15 at 06:49

serenesat

4,611
10
37
53

answered Oct 21 '14 at 17:16

hemanth

1
1

score 0 · Answer 14 · answered Nov 28 '14 at 08:37

0

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. for getting highest salary of employee.

answered Nov 28 '14 at 08:37

Vijay Bhatt

1,351
13
13

Yes. It Works in MySQL. – Vijay Bhatt Dec 03 '15 at 08:53

score 0 · Answer 15 · answered Aug 05 '16 at 16:17

0

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.

answered Aug 05 '16 at 16:17

mahesh

19
2
7

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

score 0 · Answer 16 · answered Oct 19 '16 at 03:59

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!

score 0 · Answer 17 · answered Jan 07 '17 at 06:43

0

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().

answered Jan 07 '17 at 06:43

Md Wasi

479
3
16

score 0 · Answer 18 · answered Jun 18 '17 at 08:19

0

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.

answered Jun 18 '17 at 08:19

Abhijeet Ahuja

5,596
5
42
50

score 0 · Answer 19 · answered Nov 16 '17 at 10:21

0

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)

answered Nov 16 '17 at 10:21

Shri

27
1
8

score 0 · Answer 20 · edited Dec 04 '17 at 11:58

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.

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.

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

score 0 · Answer 21 · edited Dec 10 '17 at 19:56

0

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

Just enter nth number which you want.

edited Dec 10 '17 at 19:56

Petter Friberg

21,252
9
60
109

answered Dec 10 '17 at 18:46

Swapnil Bharti

1

Yazeed M. Nashad · Answer 22 · 2018-02-09T16:02:43.693

0

Try this:

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

edited Feb 09 '18 at 16:02

answered Feb 09 '18 at 15:54

Yazeed M. Nashad

1
1

score 0 · Answer 23 · answered Jun 29 '18 at 08:57

0

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.

answered Jun 29 '18 at 08:57

Vinay

41
3

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

score 0 · Answer 24 · edited Feb 19 '20 at 16:38

0

select * from (select rownum as rownumber,emp1.* from (select * from emp order by sal desc) emp1) where rownumber = 3;

edited Feb 19 '20 at 16:38

Plutian

2,276
3
14
23

answered Feb 19 '20 at 16:18

GYANENDRA KUMAR PANDEY

27
2

4

Hi and welcome to stackoverflow, and thank you for answering. While this code might answer the question, can you consider adding some explanation for what the problem was you solved, and how you solved it? This will help future readers to understand your answer better and learn from it. – Plutian Feb 19 '20 at 16:39

score 0 · Answer 25 · edited Jun 23 '20 at 06:57

0

Try this one :

Select sal 
From (Select rownum as rank, empno,ename,sal
      From (Select * 
            From emp order by sal desc)
      ) 
where rank=2;

Just add the number as rank which will give you nth highest salary.

edited Jun 23 '20 at 06:57

Amirhossein

1,148
3
15
34

answered Jun 22 '20 at 21:43

Ambuj Dubey

1
1

score 0 · Answer 26 · answered Aug 30 '21 at 01:22

0

SELECT MIN(Salary) salary
FROM    (
            SELECT DISTINCT Salary 
            FROM Employee
            ORDER BY Salary DESC
        )
WHERE ROWNUM <= n
ORDER BY salary ASC;

-- replace "n" from line number 7 with anything you want

answered Aug 30 '21 at 01:22

Ishan Jawade

21
4

score 0 · Answer 27 · answered Sep 24 '21 at 06:16

0

select MIN(salary) from (select distinct salary from employees order by SALARY DESC) WHERE ROWNUM <= 3;

answered Sep 24 '21 at 06:16

shiva jallu

1

score -1 · Answer 28 · edited Jul 06 '15 at 09:17

-1

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.

edited Jul 06 '15 at 09:17

dhh

4,289
8
42
59

answered Jul 06 '15 at 06:14

chandan

1

1

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 06 '15 at 06:24

score -1 · Answer 29 · edited Jul 12 '15 at 05:15

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

Nth max salary in Oracle

29 Answers29

Linked

Related