SQL Join to get value belong with most recent date

Question

I have a table

employee

emp_id, ~10 other fields
a1
b1

emp_type

emp_id, effective_date, employee_type
a1            2/1/2011        RS
a1            9/3/2011        TU
b1            3/2/2011        RS

I'm doing something likke this:

select emp_id, ~10 other fields, employee_type
from employee e
inner join emp_type et
   on et.emp_id = e.emp_id
      and effective_date = (select max(effective_date) 
                            from emp_type et2 
                            where et2.emp_id = et.emp_id)

Is this the most efficient way of doing this? It doesn't run too slow, but it's a very common query in reports and I want to have the most efficient method. The emp_type has at most 4-5 records per employee, but usually just one. There are about 20,000 employees. I've seen a cross-apply solution but found that to be slower.

Answer 1

I think it is the most efficient way.

please dont forget to put indexes on the join fields and on the datetime field.

Answer 2

I think a JOIN on a subquery would be more efficient - the whole result set is calculated once instead of once per row:

select emp_id, ~10 other fields, employee_type
from employee e
inner join emp_type et
   on et.emp_id = e.emp_id
Inner join (SELECT emp_id, MAX(effective_date) effective_date
            FROM Emp_type et2
            GROUP BY Emp_id) SubQ
ON SubQ.emp_id = et.emp_id
AND SubQ.effective_date = et.effective_date

Answer 3

I suggest creating a view that contains only the newest employee type:

CREATE VIEW active_emp_type
AS
Select a.emp_id, a.effective_date, a.employee_type
from emp_type a
where a.effective_date = (select max(effective_date) from emp_type 
                          where emp_id = a.emp_id)

(Or another method of getting the same resulting view data)

Then using it elsewhere (numerous reports, etc.) is easier:

select e.emp_id, ~10 other fields, t.employee_type
from employee e
join active_emp_type t on e.emp_id = t.emp_id