Nth max salary in Oracle

oracle, oracle11g, sql

Solution

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

Problem

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.

Original source

Related problems