Let's transpose this problem to an equivalent one on the
emptable, to make it easier for you to run the queries below in your own database2. On the
emptable, a similar problem is to find for each job, the employee with the most recent hire date3. This can be done with nested queries:
A bit complicated isn't it? This is where Oracle's
select e1.* from emp e1, ( select job, max(hiredate) hiredate from emp group by job ) e2 where e1.job = e2.job and e1.hiredate = e2.hiredate ;
dense_rankcomes in. This analytic function allows you to rank data. The following adds a rank column, and for each job, the employee with the latest hire date will get a rank of 1, the employee hire just before that a rank of 2, and so on.
From here, we just need to keep only the employee with
select e.*, dense_rank() over (partition by job order by hiredate desc) rank from emp e;
rank = 1to get the expected result:
select * from (select e.*, dense_rank() over (partition by job order by hiredate desc) rank from emp e) where rank = 1 ;
1 This allows auditing and allows an admin to retrieve older version of the data if necessary.
2 In Oracle, this table is normally found in the scott schema, and you don't have it, or would like to import it in another database, you can download the SQL to create the table and add data.
3 For the purpose of this discussion, we'll assume there is only one employee for each job with the most recent hire date.