CODEWITHSUNDEEP
mysqlsqlselectmax
Greg Holmes
Greg Holmes

Reputation: 139

mysql - select newest value of groups by date

I have a table, member_track, that tracks website visits from members. From the looks of it, it tracks every load.

I need to produce a result set of just the newest visit from each member.

Here is a sample of the data:

member_id   last_accessed
    1413        2015-5-26  8:57
    1413        2015-5-26  8:54
    1413        2015-5-26  8:50
    1413        2015-5-26  8:48
    1413        2015-5-26  8:20
    1413        2015-5-26  8:17
    1412        2015-4-30  7:34
    1412        2015-4-30  7:33
    1411        2015-4-18  15:51
    1411        2015-4-18  15:50

The output would need to be:

member_id   last_accessed
    1413        2015-5-26  8:57
    1412        2015-4-30  7:34
    1411        2015-4-18  15:51

(last_accessed is a datetime field)

I have tried various things with group by and max, but am not having any success. How should I approach this?

Upvotes: 1

Views: 63

Answers (2)

shubhamagiwal92
shubhamagiwal92

Reputation: 1432

You Can try out the following Query.

SELECT member_id,last_accessed
  FROM member_track
 WHERE last_accessed in(
     SELECT Max(last_accessed)
       FROM member_track
     GROUP BY member_id)

Upvotes: 1

Mureinik
Mureinik

Reputation: 312146

You just need to query max(last_accessed) per (i.e., grouped by) member_id:

SELECT   member_id, max(last_accessed)
FROM     member_track
GROUP BY member_id

Upvotes: 2

Related Questions