sql - Not able to get the required output based on job -
i have emp table regular 14 rows. want write query scan table , return in following way means return output based on job. if first sees president returns row scans next job sees manager , returns , not return other manager , on. each new job sees returns row , pass on 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
this return first person (by alphabetically ordered last name) each job:
select employee_id, last_name, job_title, manager_id, hire_date, salary, department_id ( select e.employee_id, e.last_name, j.job_title, e.manager_id, e.hire_date, e.salary, e.department_id, row_number() on ( partition e.job_id order e.last_name ) rn hr.employees e inner join hr.jobs j on ( e.job_id = j.job_id ) ) rn = 1;
output:
(my employees
table has 109 rows... there 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
Comments
Post a Comment