1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
|
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as mni
SQL>
SQL> Select d.department_id, e.employee_id, e.job_id
2 From hr.departments d
3 Join
4 hr.job_history e
5 On ( d.department_id = e.department_id
6 )
7 Order by 1, 2
8 /
DEPARTMENT_ID EMPLOYEE_ID JOB_ID
------------- ----------- ----------
20 201 MK_REP
50 114 ST_CLERK
50 122 ST_CLERK
60 102 IT_PROG
80 176 SA_REP
80 176 SA_MAN
90 200 AC_ACCOUNT
90 200 AD_ASST
110 101 AC_MGR
110 101 AC_ACCOUNT
10 rows selected
SQL> Select d.department_id, e.employee_id, e.job_id
2 From hr.departments d
3 Join
4 hr.job_history e
5 On ( d.department_id = e.department_id
6 And e.job_id = (Select Max(e.job_id)
7 from hr.job_history e1
8 where e1.employee_id = e.employee_id
9 )
10 )
11 Order by 1, 2
12 /
DEPARTMENT_ID EMPLOYEE_ID JOB_ID
------------- ----------- ----------
20 201 MK_REP
50 114 ST_CLERK
50 122 ST_CLERK
60 102 IT_PROG
80 176 SA_REP
80 176 SA_MAN
90 200 AC_ACCOUNT
90 200 AD_ASST
110 101 AC_MGR
110 101 AC_ACCOUNT
10 rows selected
SQL> |
Partager