Find Last Updated By And Last Update Date

I was told to come up with a solution for the following scenario. I need to return the 'Last Update Date' and 'Last Updated By' columns based on the 'EmployeeID'. The query must s

Solution 1:

This will return 2 rows for the same employee if on 2 different tables 2 different users updated an employee on the same day, as required.

select employee_id,
       last_updated_dt,
       last_updated_by
  from (select x.*,
               max(last_updated_dt) over(partitionby employee_id) as max_last_updated_dt
          from (select employee_id,
                       last_updated_dt,
                       last_updated_by
                  from employee
                unionallselect employee_id,
                       last_updated_dt,
                       last_updated_by
                  from employee_award
                unionallselect employee_id,
                       last_updated_dt,
                       last_updated_by
                  from employee_address) x) x
 where last_updated_dt = max_last_updated_dt

Fiddle: http://sqlfiddle.com/#!3/a4503c/1/0

You should not avoid using union all. The data is in three different tables so this is an appropriate application of union all.

Solution 2:

Why would you want to use UNION ALL? JOINs will do your work just fine.

SELECT e.employee_id,
       e.last_updated_dt AS employee_update_date,
       e.last_updated_by AS employee_update_user,
       aw.last_updated_dt AS awards_update_date,
       aw.last_updated_by AS awards_update_user,
       ad.last_updated_dt AS address_update_date,
       ad.last_updated_by AS address_update_user
FROM employee e
LEFT JOIN employee_awards aw ON e.employee_id = aw.employee_id
LEFT JOIN address ad ON e.employee_id = ad.employee_id

I've used LEFT JOINs for robustness because I assume not all employees have entries in all tables.