Return date with maximum 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 Employee.Status.History and Employee.Status.XREF , where Client , Employee and Date correspond.

Return only the entry from Employee.Status.History with the maximum date (on Client on Employee ) In this case, it will return

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

Answer 1

Using a subselect and a group with:

 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 internal query selects the most recent date.
An external query returns the entire record on this date for the corresponding customer and employee.

Answer 2

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 

Answer 3

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 think...

Answer 4

why not use a connection 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