2 Replies Latest reply: May 9, 2012 5:03 AM by ShankarViji RSS

    to get top 5 highest sal every dept

    830484
      I HAVE EMPLOYEE table with empum,empname,deptid and sal.there are three dept.for every dept there are 20 employees.i need to get top 5 sal in every dept


      THANKS
      shilpa
        • 1. Re: to get top 5 highest sal every dept
          Frank Kulash
          Hi, Shilpa,

          Search for Top-N Query and you'll get lots of examples, such as
          http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom.html

          Most of the examples use either the analytic RANK or ROW_NUMBER functions. If you're really interested in the 5 highest salaries (rather than, say, the 5 highest paid employees) then you probably want the DENSE_RANK function instead.

          If you have trouble, post your best attempt here, with a specific question (including any error message).
          If your query doesn't use commonly available tables (like those in the scott or hr schemas), then post CREATE TABLE and INSERT statements for some sample data. Either way, post the results you want from that data.
          Always say what version of Oracle you're using.
          • 2. Re: to get top 5 highest sal every dept
            ShankarViji
            Dear,

            Here it is,

            SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
            FROM (SELECT ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY sal DESC) rn,
            empno, ename, job, mgr, hiredate, sal, comm, deptno
            FROM emp)
            WHERE rn = 1;


            Where rn=1 lists all the top employees in Each Department.

            For yours, where rn<=5 will list the top 5 Employees in each Dept.