CODEWITHSUNDEEP
sqloracle-database
Lara
Lara

Reputation: 49

Not able to get the required output based on job

I have emp table with all regular 14 rows. I want to write a query to scan the table and return in the following way it means it will return the output based on job. If it first sees President it returns that row then it scans to next job it sees manager and returns that and not return any other manager and so on. so for each new job it sees it returns that row and pass on to next new job. Thanks

7839    KING    PRESIDENT       17-NOV-81   5000        10
7698    BLAKE   MANAGER 7839    01-MAY-81   2850        30
7788    SCOTT   ANALYST 7566    19-APR-87   3000        20
7369    SMITH   CLERK   7902    17-DEC-80   800         20

Please help

thanks

Upvotes: 0

Views: 42

Answers (1)

MT0
MT0

Reputation: 167981

This will return the first person (by alphabetically ordered last name) for each job: 

SELECT EMPLOYEE_ID,
       LAST_NAME,
       JOB_TITLE,
       MANAGER_ID,
       HIRE_DATE,
       SALARY,
       DEPARTMENT_ID
FROM   (
        SELECT e.EMPLOYEE_ID,
               e.LAST_NAME,
               j.JOB_TITLE,
               e.MANAGER_ID,
               e.HIRE_DATE,
               e.SALARY,
               e.DEPARTMENT_ID,
               ROW_NUMBER() OVER ( PARTITION BY e.JOB_ID ORDER BY e.LAST_NAME ) AS RN
        FROM   HR.EMPLOYEES e
               INNER JOIN
               HR.JOBS j
               ON ( e.JOB_ID = j.JOB_ID )
      )
WHERE RN = 1;

Output:

(My EMPLOYEES table has 109 rows... there are 19 different jobs though)

EMPLOYEE_ID LAST_NAME                 JOB_TITLE                           MANAGER_ID HIRE_DATE     SALARY DEPARTMENT_ID
----------- ------------------------- ----------------------------------- ---------- --------- ---------- -------------
        206 Gietz                     Public Accountant                          205 07-JUN-02       8300           110 
        205 Higgins                   Accounting Manager                         101 07-JUN-02      12008           110 
        200 Whalen                    Administration Assistant                   101 17-SEP-03       4400            10 
        100 King                      President                                      17-JUN-03      24000            90 
        102 De Haan                   Administration Vice President              100 13-JAN-01      17000            90 
        110 Chen                      Accountant                                 108 28-SEP-05       8200           100 
        108 Greenberg                 Finance Manager                            101 17-AUG-02      12008           100 
        203 Mavris                    Human Resources Representative             101 07-JUN-02       6500            40 
        105 Austin                    Programmer                                 103 25-JUN-05       4800            60 
        201 Hartstein                 Marketing Manager                          100 17-FEB-04      13000            20 
        202 Fay                       Marketing Representative                   201 17-AUG-05       6000            20 
        204 Baer                      Public Relations Representative            101 07-JUN-02      10000            70 
        116 Baida                     Purchasing Clerk                           114 24-DEC-05       2900            30 
        114 Raphaely                  Purchasing Manager                         100 07-DEC-02      11000            30 
        148 Cambrault                 Sales Manager                              100 15-OCT-07      11000            80 
        174 Abel                      Sales Representative                       149 11-MAY-04      11000            80 
        192 Bell                      Shipping Clerk                             123 04-FEB-04       4000            50 
        130 Atkinson                  Stock Clerk                                121 30-OCT-05       2800            50 
        121 Fripp                     Stock Manager                              100 10-APR-05       8200            50

Upvotes: 2

Related Questions