userimage

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.

userimage

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.

Answer is