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 lt  b sal   amp 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

User · Answer

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

User · Answer

Try this   SELECT min sal   FROM   SELECT sal FROM emp ORDER BY sal desc  WHERE ROWNUM  lt   3  -- Replace 3 with any value of N

User · Answer

SELECT Min sal  FROM    SELECT DISTINCT sal         FROM   emp         WHERE  sal IS NOT NULL         ORDER  BY sal DESC  WHERE  rownum  lt   n

User · Answer

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

User · Answer

select min sal  from  select distinct sal from employee order by sal DESC  where rownum lt  N   place the number whatever the highest sal you want to retrieve

User · Answer

SELECT    FROM Employee Emp1 WHERE  N-1       SELECT COUNT DISTINCT Emp2 Salary   FROM Employee Emp2 WHERE Emp2 Salary  gt  Emp1 Salary

User · Answer

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

User · Answer

select   from  select rownum as rownumber emp1   from  select   from emp order by sal desc  emp1  where rownumber   3

User · Answer

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

User · Answer

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 lt  b sal   amp 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 gt  b sal   amp n

User · Answer

select min sal  from  select distinct sal  from emp  order by sal desc  where rownum  lt   amp 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

User · Answer

5th highest salary   SELECT        FROM     emp a  WHERE      4             SELECT              COUNT DISTINCT b sal           FROM              emp b          WHERE              a sal  lt  b sal         Replace 4 with any value of N

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT sal     FROM                   SELECT empno                               deptno  sal                                dense rank    over   partition by deptno order by sal desc  NRANK                 FROM emp                   WHERE NRANK   4

User · Answer

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

User · Answer

Select min salary  from     select distinct salary  from empdetails order by salary desc   where rownum  lt   amp rn   Just enter nth number which you want

User · Answer

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  lt  5   select   from salary a  where rowid    select min rwid  from data

User · Answer

We could write as below mentioned also  select min sal  from  select sal from emp where rownum  lt  amp n order by sal desc

User · Answer

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

User · Answer

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

User · Answer

SELECT       FROM                   SELECT empno                         deptno  sal                         dense rank    over   order by sal desc  NRANK                 FROM emp                   WHERE NRANK   4

User · Answer

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  gt  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  lt   n

User · Answer

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

User · Answer

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 lt  5  or  where rownum lt  6 and so on     select min sal  from select distinct sal  from emp  where rownum lt  3 order by sal desc

[sql] Nth max salary in Oracle

Examples related to sql

Examples related to oracle

Examples related to oracle11g