Q:

Mysql - 'Select max' from multiple joined tables doesn't return correct values

I have two tables - one is a list of addresses, and the other of attendance dates and EmployeeIDNumbers to identfy the engineer who attended.  An engineer may have attended an address multiple times.  I am trying to select the address name, and the most recent attendance date and corresponding engineerID 
select s.sitename, max(sd.scheduleddate), sd.EngineerID
from sites as s 
left join scheduled_dates as sd on sd.idsites = s.idsites
group by s.idsites

This code correctly pulls each address and the most recent 'Scheduled Date' but does not pull the correct corresponding engineer id.  How do I get the engineerID from the same row as the max(scheduleddate)?  Think this is something to do with the 'greatest-n-per-group' discussion, but I can't see how to implement that code with a query that already has a join

A:

You can use a NOT EXISTS condition with a correlated subquery:
select s.sitename, sd.EngineerID, sd.scheduleddate
from sites as s 
inner join scheduled_dates as sd on sd.idsites = s.idsites
where not exists (
    select 1
    from scheduled_dates sd1
    where sd1.idsites = s.idsites
    and sd1.scheduleddate > sd.scheduleddate
)

The condition ensures that there no other record exists in scheduled_dates for the current site with a date greater than the one on the record being selected.
Notes: I turned you LEFT JOIN to an INNER JOIN, since I believe that it better fit your use cases, feel free to revert this if needed.

