甲骨文的第N个最高薪水 [英] Nth max salary in Oracle

问题描述

要使用以下查询找出我在oracle中的第N个最大工资

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.

    推荐答案

    尝试一下

    select *
      from
      (
        select
            sal
              ,dense_rank() over (order by sal desc) ranking
        from   table
      )
      where ranking = 4 -- Replace 4 with any value of N
    

    这篇关于甲骨文的第N个最高薪水的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！