Joseph Erickson Joseph Erickson - 2 years ago 82
SQL Question

mysql highly selective query

I have a data set like this:

User Date Status
Eric 1/1/2015 4
Eric 2/1/2015 2
Eric 3/1/2015 4
Mike 1/1/2015 4
Mike 2/1/2015 4
Mike 3/1/2015 2

I'm trying to write a query in which I will retrieve users whose MOST RECENT transaction status is a 4. If it's not a 4 I don't want to see that user in the results. This dataset could have 2 potential results, one for Eric and one for Mike. However, Mike's most recent transaction was not a 4, therefore:

The return result would be:

User Date Status
Eric 3/1/2015 4

As this record is the only record for Eric that has a 4 as his latest transaction date.

Here's what I've tried so far:

user, MAX(date) as dates, status

This would get me to a unqiue record for every user for every status type. This would be a subquery, and the parent query would look like:

user, dates, status
status = 4

However, this is clearly flawed as I don't want status = 4 records IF their most recent record is not a 4. I only want status = 4 when the latest date is a 4. Any thoughts?

Answer Source
SELECT user, date 
    , actualOrders.status
   SELECT user, MAX(date) as date
   FROM orders
   GROUP BY user) AS lastOrderDates
INNER JOIN orders AS actualOrders USING (user, date)
WHERE actualOrders.status = 4
-- Since USING is being used, there is not a need to specify source of the
-- user and date fields in the SELECT clause; however, if an ON clause was
-- used instead, either table could be used as the source of those fields.

Also, you may want to rethink the field names used if it is not too late and user and date are both found here.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download