8 Replies Latest reply: Jan 22, 2007 12:25 AM by orawarebyte RSS

    Nth Maximum Salary

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

      Veerendar
        • 1. Nth Maximum Salary
          3004
          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
            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
              Thanx Prem Kumar & MuthuPandian
              null
              • 4. Nth Maximum Salary
                3004
                Thanx Prem Kumar & MuthuPandian
                null
                • 5. Nth Maximum Salary
                  3004
                  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
                    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
                      Hi,
                      Its toooooo late.. but u can use the technic connect by prior for the same.
                      • 8. Re: Nth Maximum Salary
                        orawarebyte
                        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