select max datetime return wrong values

Question

I know there are many topics related to this. I tried researching on them, but I couldn't get this right.

I have a following lunch_transaction table:

id  |  user_id  |  date               |  due_amount  |  updated_on
---------------------------------------------------------------------------
1   |      145  | 2018-05-28 12:56:46 |   30.00      | 2018-05-28 12:56:46
2   |      134  | 2018-05-28 13:33:02 |   30.00      | 2018-05-28 13:33:02
3   |      134  | 2018-05-28 13:38:50 |   60.00      | 2018-05-28 13:38:50
4   |      134  | 2018-05-28 13:38:59 |   60.00      | 2018-05-28 13:38:59
5   |      134  | 2018-05-30 13:39:17 |   60.00      | 2018-05-28 13:39:17
6   |      145  | 2018-05-30 15:20:00 |   45.00      | 2018-05-28 15:00:17

I want to get latest date-time record for each user.

Hence desired output should be as :

145  | 2018-05-30 15:20:00 |   45.00      | 2018-05-28 15:00:17
134  | 2018-05-30 13:39:17 |   60.00      | 2018-05-28 13:39:17

But I'm getting the following output:

145  | 2018-05-30 15:20:00 |   30.00      | 2018-05-28 12:56:46
134  | 2018-05-30 13:39:17 |   30.00      | 2018-05-28 13:33:02

Even the column values are not of the same row, i.e. seems like field values in the result-set are mixed from different rows.

Here's my query:

SELECT user_id, MAX(date), due_amount, updated_on 
FROM lunch_transaction 
GROUP BY user_id

What should be the right query to achieve the desired output?

Take a look at https://stackoverflow.com/questions/5986127/do-all-columns-in-a-select-list-have-to-appear-in-a-group-by-clause to get you going in the right direction. — Sam M, May 28 '18 at 15:47
Try changing `MAX(date)` for only `date` and at the end add `ORDER BY date desc` — WiLLyxVKei, May 28 '18 at 15:49
Use proper `GROUP BY`. Put all non-aggregated columns in `GROUP BY`. Once you fix it, you would probably find out what's wrong with your query. — Eric, May 28 '18 at 15:49

score 1 · Accepted Answer · answered May 28 '18 at 15:52

Your current query is on the right track, but to make that approach work you'll need to join your original table to it, to filter off any records per user which are not the max ones:

SELECT t1.*
FROM lunch_transaction t1
INNER JOIN
(
    SELECT user_id, MAX(date) AS max_date
    FROM lunch_transaction 
    GROUP BY user_id
) t2
    ON t1.user_id = t2.user_id AND t1.date = t2.max_date;

score 1 · Answer 2 · answered May 29 '18 at 17:38

Since, you have identity (id) column then you can take advantage of it via subquery with limit clause:

select lt.*
from lunch_transaction lt
where id = (select lt1.id
            from lunch_transaction lt1
            where lt.user_id = lt1.user_id
            order by date lt1.desc
            limit 1
           );

select max datetime return wrong values

2 Answers2