Return record with max date

Question

Table – Employee.Status.XREF

Client  Employee Date
198  Jon Doe  3/1/2009
198  Jon Doe  1/1/2009

Table – Employee.Status.History

Client  Employee Date  Status
198  Jon Doe  5/21/2009 T 
198  Jon Doe  3/1/2009 A
198  Jon Doe  1/1/2009 P

Only query records in both Employee.Status.History and Employee.Status.XREF where Client, Employee and Date match. Return only the record from Employee.Status.History with the max date (by client by employee) In this case it would return

Client  Employee Date  Status
198  Jon Doe  3/1/2009 A

Answer 1

MySql:

SELECT b.* FROM tableXREF a, tableSTATUS b WHERE b.client = a.client ORDER BY b.Date DESC LIMIT 1

MsSql: SELECT TOP(1) b.field1, b.field2 etc. FROM tableXREF a, tableSTATUS b WHERE b.client = a.client ORDER BY b.Date DESC

I guess ...

Answer 2

Using a sub-select & group by:

select b.*  
from Employee.Status.History b,  
( select client, employee, max(date) date  
  from Employee.Status.XREF  
  group by client, employee  
) a  
where b.client = a.client  
and   b.employee = a.employee  
and   b.date = a.date

The inner query selects the most recent date.
The outer query returns the entire record on that date for the associated client and employee.

Answer 3

why not use a join with a group by clause?

select h.client,h.employee,max(h.date),h.Status from Employee.Status.History h inner join Employee.Status.XREF x on h.client=x.client and h.employee=x.employee and h.date=x.date group by h.client,h.employee,h.Status

Answer 4

This should work:

SELECT h.*
FROM Employee.Status.History h
INNER JOIN (
    SELECT h.Client, MAX(h.Date) AS MaxDate
    FROM Employee.Status.History h
    INNER JOIN Employee.Status.XREF x ON h.Client = x.Client 
         AND h.Employee = x.Employee AND h.Date = x.Date
) q ON h.Client = q.Client