CODEWITHSUNDEEP
mysqlgroup-bysql-order-by
Gerard Nijboer
Gerard Nijboer

Reputation: 512

MySQL query with GROUP BY and ORDER BY timestamp DESC

I am saving the history of Facebook likes for a page, identified by user_id.

Now from this table, I need to get a set representing the user_id's and their latest number of likes, based on the most recent timestamp.

I started off with this:

SELECT * 
FROM facebook_log 
GROUP BY user_id 
ORDER BY timestamp DESC;

But that does not do what I want because it returns the first records with the lowest timestamps.

I read something online about GROUP returning the very first records from the table.

I also understood something about JOIN the table with itself, but that doesn't work either, or I did something wrong.

Upvotes: 0

Views: 3208

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270683

You can also get the latest number of likes by using this MySQL trick:

select f.user_id, max(f.timestamp),
       substring_index(group_concat(f.numlikes order by f.timestamp desc), ',', 1) as LatestLikes
from facebook_log f
group by f.user_id;

Upvotes: 0

Raphaël Althaus
Raphaël Althaus

Reputation: 60503

If you just need the user_id and the timestamp, you can just do

select f.user_id, max(f.timestamp)
from facebook_log
group by user_id;

if you need all the data from the table, you can do

select f.*
from facebook_log f
inner join (select max(timestamp) mt, user_id
            from facebook_log 
            group by user_id) m
on m.user_id = f.user_id and m.mt = f.timestamp

Upvotes: 3

Related Questions