MySQL Query - select a join if the most recent record in the join table contains a specific value

I am trying to write a select statement with a right join (to clients), that will find a specific value in the join table - but ONLY if that is the most recent value for each client (ignoring blanks and nulls).

  Clients
  Id    Name
   0     John Doe
   1     Frank Smith
   2     Sue Smith
   3     John Smith

 Activity   (join table)
 ClientId  Type   Date
   0       500   2013-01-01 00:00:08
   1       900   2013-01-01 00:00:07
   2       NULL  2013-01-01 00:00:06
   3             2013-01-01 00:00:05
   4       500   2013-01-01 00:00:05
   0       800   2013-01-01 00:00:04
   1       500   2013-01-01 00:00:03
   2       500   2013-01-01 00:00:02
   3       500   2013-01-01 00:00:01
   4       800   2013-01-01 00:00:00

So this query will at least give me only the client records that have an activity type of 500 (in this case I would get back client 0 and 4):

select * from clients right join activity on activity.clientid = clients.id
where activity.type = 500

HOWEVER, I need to figure out how to make this return ONLY the first record in the above list of records. The logic there is Client #0 is the only client that has 500 as it's latest activity type = 500. The other 3 clients have NULL, blank, or 900 for example as their 'latest' activity type.

I am thinking some magic with ordering (the date would normally be pretty accurate), a 'top' and/or 'limit' and possibly union? Just cant quite wrap my head around it.

Please try this

SELECT activity.id AS activityid
    , activity.type
    , activity.date
    , clients.id AS clientid
    , clients.name
FROM activity
LEFT JOIN activity AS other_activities
  ON activity.ClientID  = other_activities.ClientID
    AND activity.date < other_activities.date
LEFT JOIN clients
  ON activity.ClientID = clients.id
WHERE activity.type = 500
  AND other_activities.ClientID IS NULL;

Related Articles