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.

Reputation: 689

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.

Upvotes: 3

Answers (29)

shiva jallu

Reputation: 1

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

Upvotes: 0

Ishan Jawade

Reputation: 21

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

Upvotes: 0

Sachin Jawale

Reputation: 21

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

Upvotes: 1

Yash

Reputation: 650

We could write as below mentioned also.

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

Upvotes: 1

Sandeep KVS

Reputation: 11

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

Upvotes: 1

Ambuj Dubey

Reputation: 1

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.

Upvotes: 0

GYANENDRA KUMAR PANDEY

Reputation: 27

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

Upvotes: 0

Sneha

Reputation: 21

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

Upvotes: 2

Vinay

Reputation: 41

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.

Upvotes: 0

Yazeed M. Nashad

Reputation: 1

Try this:

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

Upvotes: 0

Swapnil Bharti

Reputation: 1

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

Just enter nth number which you want.

Upvotes: 0

Naga Ramesh Busam

Reputation: 31

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

Upvotes: 0

Shri

Reputation: 27

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)

Upvotes: 0

Seth Projnabrata

Reputation: 143

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
)

Upvotes: 1

Abhijeet Ahuja

Reputation: 5950

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.

Upvotes: 0

Hasnain Latif

Reputation: 65

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);

Upvotes: 2

Md Wasi

Reputation: 503

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

Upvotes: 0

Akshay Lokur

Reputation: 7506

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!

Upvotes: 0

mahesh

Reputation: 19

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.

Upvotes: 0

hemanth

Reputation: 1

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;

Upvotes: 0

Balamurugan B

Reputation: 1

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

Upvotes: -1

chandan

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

Upvotes: -1

Vijay Bhatt

Reputation: 1391

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.

Upvotes: 0

Kishore Kumar

Reputation: 939

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

Upvotes: 0

siva

Reputation: 51

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

Upvotes: 5

Anant_00

Reputation: 143

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

Upvotes: 0

Anant_00

Reputation: 143

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

Upvotes: 0

Kombajn zbożowy

Reputation: 10693

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.

Upvotes: 6

vhadalgi

Reputation: 7189

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

Upvotes: 25

Nth max salary in Oracle

Answers (29)

Related Questions