Nth max salary in Oracle

ghz 1years ago ⋅ 9665 views

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.

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