CODEWITHSUNDEEP
mysql
Gunaseelan
Gunaseelan

Reputation: 2542

Getting result for latest row(based on timestamp column) for each user and each day

I have a table called play_progress.

play_progress

id     user_id  coins      timecreated

1      1          20       2016-01-23 06:55:09
2      1          24       2016-01-23 06:59:22 
3      1          28       2016-01-23 07:05:34
4      2          4        2016-01-23 07:10:58
5      2          10       2016-01-23 07:12:08
6      1          32       2016-01-24 00:07:48
7      2          14       2016-01-24 00:12:08
8      1          35       2016-01-24 00:44:48
9      2          18       2016-01-24 00:55:08

I like to get the latest row( based on timecreated) for each day for each user.

I have tried the following query;

SELECT user_id, coins, MAX(timecreated) 
FROM player_progress 
GROUP BY user_id, DATE(timecreated);

It gives the result for each day but it gives wrong timecreatd value.

Where I am going wrong?

Result details like

id    user_id  coins   timecreated
1       1        28    2016-01-23 07:05:34
5       2        10    2016-01-23 07:12:08
8       1        35    2016-01-24 00:44:48
9       2        18    2016-01-24 00:55:08

I have searched through SO, but couldn't find a solution to my problem.

Upvotes: 0

Views: 53

Answers (1)

moni_dragu
moni_dragu

Reputation: 1163

If you need to fetch more columns then the ones used for grouping (user_id and timecreatd) you need a more complex query:

SELECT p.user_id, p.id, coins, p.timecreated
FROM play_progress p INNER JOIN
(SELECT user_id, MAX(timecreated) as max_time 
 FROM play_progress
 GROUP BY user_id, DATE(timecreated)) pp
   ON p.user_id = pp.user_id 
   AND p.timecreated = pp.max_time;

Here you have a sample

Upvotes: 3

Related Questions