sql - 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<=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 1

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