MySQL query help (semi-unique results)

Question

user_id | user_destination | user_date_out | user_date_in | user_purpose | uid
0095    | NYC              | 2010-11-25    | 2010-11-26   | Work         | 1
0105    | Seattle          | 2010-11-15    | 2010-11-20   | Work         | 2
0095    | Home             | 2010-11-10    | 2010-11-11   | Personal     | 3
0123    | Nashville        | 2010-11-10    | 2010-11-12   | Doctober     | 4

I have the above data in a MySQL table. I need a query that will output the row if it is the most recent user_date_out for that user_id. Hard to explain, but the rows that should be displayed via the query are uid 2,3 and 4 (UID 1 would fall off because the user_date_out is "older" than UID 3 for the same user). Can anyone help me with this? Thanks in advance!

EDIT: I solved it by using the following query:

SELECT *
FROM `table`
WHERE `uid` = (
    SELECT `uid`
    FROM `table` as `alt`
    WHERE `alt`.`user_id` = `table`.`user_id`
    ORDER BY `user_date_out`
    LIMIT 1
)
ORDER BY `user_date_out`

Found this: http://stackoverflow.com/questions/924494/mysql-group-by-ordering — krx, Nov 10 '10 at 23:06
@krio, that helped!! I removed the last "limit 3" and removed DESC from the order, and I got the results I needed! Thanks for all your help! — Trevor, Nov 10 '10 at 23:14

krx · Accepted Answer · 2010-11-11T00:33:35.780

3

Per request

SELECT *
FROM `table`
WHERE `uid` = (
    SELECT `uid`
    FROM `table` as `alt`
    WHERE `alt`.`user_id` = `table`.`user_id`
    ORDER BY `user_date_out`
    LIMIT 1
)
ORDER BY `user_date_out`

edited Nov 11 '10 at 00:33

answered Nov 10 '10 at 22:39

krx

2,643
4
28
29

That will only return uid 3. I need to return 2, 3 and 4. This is obviously just example data, so I cannot just "select wehre user_id='0105' and '0123'". Hope this makes a little sense. It's confusing for me to try to explain. Thanks for your response. – Trevor Nov 10 '10 at 22:44
Without the where you should be good as is. I think you reviewed it right before my edit. This will gather a single record per user_id with the latest date – krx Nov 10 '10 at 22:48
I must have seen your answer right before the edit. ;-) This still doesn't work, unfortunately. I think it's because MySQL is grouping the user_id field before ordering them, so it still grabs UID 1 instead of UID 3 for user_id 0095 – Trevor Nov 10 '10 at 22:51
Try throwing a DESC at the end – krx Nov 10 '10 at 22:53

mechanical_meat · Answer 2 · 2010-11-10T23:28:12.203

SELECT t.*, a_subquery.min_user_date_out
  FROM your_table t
       JOIN (SELECT user_id, MIN(user_date_out) AS min_user_date_out 
               FROM your_table
           GROUP BY user_id) a_subquery
       ON a_subquery.min_user_date_out = t.user_date_out 
      AND a_subquery.user_id = t.user_id

Since MySQL sometimes has issues w/ subqueries, here's the same equivalent query with a self-exclusion join:

SELECT t.*
  FROM your_table t
       LEFT JOIN your_table t2 
       ON t.user_id = t2.user_id
       AND t.user_date_out > t2.user_date_out
 WHERE t2.user_id IS NULL

Note that both of these queries will not work if you have more than one of any user_date_out for a given user_id.

For some reason this only returns 1 result. I think I solved it, though! — Trevor, Nov 10 '10 at 23:13

MySQL query help (semi-unique results)

2 Answers2