This discussion is archived
8 Replies Latest reply: Jan 21, 2007 10:25 PM by 316993 RSS

Nth Maximum Salary

3004 Newbie
Currently Being Moderated
Hi all,
I want to get the Nth highest salary from emp table. Can anybody tell me?

Veerendar
  • 1. Nth Maximum Salary
    3004 Newbie
    Currently Being Moderated
    Try this query, which returns 5th position

    select sal from
    (select rownum n,a.* from
    ( select distinct sal from emp order by sal desc) a)
    where n = 5;

    prem


    null
  • 2. Nth Maximum Salary
    3004 Newbie
    Currently Being Moderated
    Hi there!

    Try with the following for n th max,

    select * from emp r1
    where &n = (select count(*) from emp
    where sal >= r1.sal)

    HTH,
    K.M.P
  • 3. Nth Maximum Salary
    3004 Newbie
    Currently Being Moderated
    Thanx Prem Kumar & MuthuPandian
    null
  • 4. Nth Maximum Salary
    3004 Newbie
    Currently Being Moderated
    Thanx Prem Kumar & MuthuPandian
    null
  • 5. Nth Maximum Salary
    3004 Newbie
    Currently Being Moderated
    try this

    select table1.d
    from
    (select x as d
    from yr_table order by x desc)table1
    where rownum <5;

    change the value of 5 to change the no of rows retrived to u.

    ashraf

    null
  • 6. Re: Nth Maximum Salary
    554648 Newbie
    Currently Being Moderated
    select * from emp
    where sal = (select distinct sal from emp x
    where &n=(select count(distinct sal) from emp where sal >= x.sal))
  • 7. Re: Nth Maximum Salary
    3293 Newbie
    Currently Being Moderated
    Hi,
    Its toooooo late.. but u can use the technic connect by prior for the same.
  • 8. Re: Nth Maximum Salary
    316993 Pro
    Currently Being Moderated
    If you are at 8i and onward then you can use analytic function to acheive the same.
    SQL> SELECT * FROM emp ORDER BY sal DESC
      2  /
    
        EMPNO ENAME      JOB             MGR HIREDATE        SAL      COMM    DEPTNO
    --------- ---------- --------- --------- --------- --------- --------- ---------
         7839 KING       PRESIDENT           17-NOV-81      5000                  10
         7788 SCOTT      ANALYST        7566 19-APR-87      3000                  20
         7902 FORD       ANALYST        7566 03-DEC-81      3000                  20
         7566 JONES      MANAGER        7839 02-APR-81      2975                  20
         7698 BLAKE      MANAGER        7839 01-MAY-81      2850                  30
         7782 CLARK      MANAGER        7839 09-JUN-81      2450                  10
         7499 ALLEN      SALESMAN       7698 20-FEB-81      1600       300        30
         7844 TURNER     SALESMAN       7698 08-SEP-81      1500         0        30
         7934 MILLER     CLERK          7782 23-JAN-82      1300                  10
         7521 WARD       SALESMAN       7698 22-FEB-81      1250       500        30
         7654 MARTIN     SALESMAN       7698 28-SEP-81      1250      1400        30
         7876 ADAMS      CLERK          7788 23-MAY-87      1100                  20
         7900 JAMES      CLERK          7698 03-DEC-81       950                  30
         7369 SMITH      CLERK          7902 17-DEC-80       800                  20
    
    14 rows selected.
    
    SQL> SELECT empno,ename,job,mgr,hiredate,sal
      2    FROM (SELECT empno,ename,job,mgr,hiredate,sal,dense_rank() OVER (ORDER BY sal  DESC) Topn
      3            FROM emp)
      4   WHERE Topn<=3
      5  /
    
        EMPNO ENAME      JOB             MGR HIREDATE        SAL
    --------- ---------- --------- --------- --------- ---------
         7839 KING       PRESIDENT           17-NOV-81      5000
         7788 SCOTT      ANALYST        7566 19-APR-87      3000
         7902 FORD       ANALYST        7566 03-DEC-81      3000
         7566 JONES      MANAGER        7839 02-APR-81      2975
    Khurram