Sai Sai - 3 months ago 15
SQL Question

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.


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
Comments