Joseph Erickson Joseph Erickson - 7 months ago 10
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:

SELECT
user, MAX(date) as dates, status
FROM
orders
GROUP BY
status,
user


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:

SELECT
user, dates, status
WHERE
status = 4
GROUP BY
user


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
SELECT user, date 
    , actualOrders.status
FROM (
   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.

Comments