mysql highly selective query

Question

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?

If `user, date` is unique, you can use a subquery to find the last dates, then join that to the table to find the status on those records. — Uueerdo, Apr 21 '16 at 17:00

Uueerdo · Accepted Answer · 2016-04-21T17:11:13.593

2

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.

edited Apr 21 '16 at 17:11

answered Apr 21 '16 at 17:03

Uueerdo

15,723
1
16
21

Where is status coming from? You're selecting it from a subquery without status? – Joseph Erickson Apr 21 '16 at 17:06
No, I am selecting it from the `JOIN`ed orders table reference; I'll update it for clarity. – Uueerdo Apr 21 '16 at 17:08
Are subselects so complex in mysql? Wouldn't something like select * from (select user,max(date),status from orders group by status,user) where status=4; work? – I_am_Batman Apr 21 '16 at 17:09
1

Well, the real point here which is really valuable is the join. I didn't think about referencing the inner join. Without that your solution @I_am_Batman would pull the MOST recent record that had a 4, not the most recent record IF it had a 4. Thus the inner join. – Joseph Erickson Apr 21 '16 at 17:10
@Uueerdo I've been studying your solution which I find to be pretty eloquent. The question I have is, on the line `INNER JOIN orders USING (user, date)` does `user, date` refer back to the first line, meaning the results of the subquery? – Joseph Erickson Apr 21 '16 at 17:29
@JosephErickson Nope, `USING` is similar to a natural join, but safer in that it only uses the fields you specify. `tbl1 INNER JOIN tbl2 USING (x, y, z)` is equivalent to `tbl1 INNER JOIN tbl2 ON tbl1.x = tbl2.x AND tbl1.y = tbl2.y AND tbl1.z = tbl2.z`. _In more complicated scenarios, the fields referenced can involve more than two tables so long each field listed is only present in two potential tables, otherwise ambiguity prevents use of the feature._ – Uueerdo Apr 21 '16 at 18:23

score 0 · Answer 2 · answered Apr 21 '16 at 17:09

0

SELECT user, date, status FROM
(
   SELECT user, MAX(date) as date, status FROM orders GROUP BY user
)
WHERE status = 4

answered Apr 21 '16 at 17:09

Mojtaba

4,852
5
21
38

`Status` chosen for the results in the inner query is officially undefined. – Uueerdo Apr 21 '16 at 17:12

score 0 · Answer 3 · edited May 23 '17 at 12:07

The easiest way is to include your order table a second time in a subquery in your from clause in order to retrieve the last date for each user. Then you can add a where clause to match the most recent date per user, and finally filter on the status.

select orders.*
from orders,
(
    select ord_user, max(ord_date) ord_date
    from orders
    group by ord_user
) latestdate
where orders.ord_status = 4
    and orders.ord_user = latestdate.ord_user
    and orders.ord_date = latestdate.ord_date

Another option is to use the over partition clause: Oracle SQL query: Retrieve latest values per group based on time

Regards,

mysql highly selective query

3 Answers3