CODEWITHSUNDEEP
mysql
nafas
nafas

Reputation: 5423

select distinct id with their latest activity

I have a MYSQL table structure as follows:

id         Activity
6298    2015-06-25 11:39:35
11141   2015-06-25 11:24:52
11141   2015-06-25 11:24:10
15048   2015-06-25 11:23:08

I want to get All ids and their latest activity :

so for the above example I want to get this:

id         Activity
6298    2015-06-25 11:39:35
11141   2015-06-25 11:24:52
15048   2015-06-25 11:23:08

at the moment I have to programatically go through each id and get their latest activity :

for each nextID in IDs :
    SELECT * FROM mytable WHERE id= nextID  ORDER BY added DESC LIMIT

Note:

the table is huge because each id has many Activity(s).

QUESTION:

so my question is if there is a better way of doing it? for example one single query that could return me each id with its latest activity.

Upvotes: 1

Views: 27

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Just do a grouping. There is no shorter/better way. This is minimal statement for this:

select id, max(Activity) as Activity
from TableName
group by id

Upvotes: 2

Related Questions