[sql] 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.

This question is related to sql oracle oracle11g

The answer is


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

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

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.


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

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


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.


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!


We could write as below mentioned also.

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

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.


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

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

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.


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


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

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

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
)

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


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.


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

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

Try this:

SELECT min(sal)  FROM (
SELECT sal FROM emp ORDER BY sal desc) WHERE ROWNUM <= 3; -- Replace 3 with any value of 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)

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.


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

Just enter nth number which you want.


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to oracle

concat yesterdays date with a specific time ORA-28001: The password has expired how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Find the number of employees in each department - SQL Oracle Query to display all tablespaces in a database and datafiles When or Why to use a "SET DEFINE OFF" in Oracle Database How to insert date values into table error: ORA-65096: invalid common user or role name in oracle In Oracle SQL: How do you insert the current date + time into a table?

Examples related to oracle11g

Convert timestamp to date in Oracle SQL Query to display all tablespaces in a database and datafiles Oracle Installer:[INS-13001] Environment does not meet minimum requirements Forgot Oracle username and password, how to retrieve? Extract number from string with Oracle function How to solve : SQL Error: ORA-00604: error occurred at recursive SQL level 1 Add days Oracle SQL How to determine tables size in Oracle ORA-28000: the account is locked error getting frequently Oracle listener not running and won't start