Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    42

    Unanswered: N th highest and lowest salary of an emp

    Hi,

    iam new to oracle, iam unable to retrieve how to select the nth highest and nth lowest salary from employee table. Can you pls suggest for getting this one.

    Thank you..

  2. #2
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hi,

    for nth highest salary
    SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
    for nth lowest salary
    SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal>=b.sal);

    Hope this will help you.

    Thanks,
    Pagnint
    (No need to search web before posting new question)

  3. #3
    Join Date
    Jan 2003
    Location
    Vienna, Austria
    Posts
    102
    what's about
    SELECT DISTINCT(sal) SALARY
    FROM EMP
    WHERE ROWNUM = &N
    ORDER BY DISTINCT(sal)
    ^/\x

  4. #4
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    Rownum is created after the data is fetched.

    So when you say, where rownum = 5, for example, this is what happens:

    Oracle fetches the data, looks at the first row, assigns it a value of 1, then compares the predicate "rownum = 5". Clearly, this fails. Then, Oracle fetches the next row, assigns it a rownum of 1 again, then checks.

    As you can see, this process will continue indefinitely and finally you get zero rows of data.

    The only case where it succeeds is when the predicate is "rownum = 1".

    As a side note, Oracle probably optimizes queries such as "rownum <> 1" and just returns zero rows.

    Ravi

  5. #5
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    This is a Top-N analysis problem:

    Code:
    SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
       FROM
       (SELECT Empno, Ename, Job, Mgr, Hiredate, Sal,
          RANK() OVER
             (ORDER BY SAL Desc NULLS LAST) AS Emp_Rank
          FROM Emp
          ORDER BY SAL Desc NULLS LAST)
       WHERE Emp_Rank = 1;
    JoeB

  6. #6
    Join Date
    Jan 2003
    Location
    China
    Posts
    38

    Talking Simply like these

    SELECT * FROM(SELECT DISTINCT sal SALARY FROM emp ORDER BY sal DESC) WHERE ROWNUM <= &N;

    SELECT * FROM(SELECT DISTINCT sal SALARY FROM emp ORDER BY sal ASC) WHERE ROWNUM <= &N;
    Last edited by 884813; 06-02-04 at 02:39.
    Fan Yi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •