How to get the latest record in each group using GROUP BY

Question

Let s say I have a table called messages with the columns   id   from id   to id   subject   message   timestamp   I want to get the latest message from each user only  like you would see in your FaceBook inbox before you drill down into the actual thread   This query seems to get me close to the result I need   SELECT   FROM messages GROUP BY from id   However the query is giving me the oldest message from each user and not the newest   I can t figure this one out

User · Answer

This is a standard problem   Note that MySQL allows you to omit columns from the GROUP BY clause  which Standard SQL does not  but you do not get deterministic results in general when you use the MySQL facility   SELECT     FROM Messages AS M   JOIN  SELECT To ID  From ID  MAX TimeStamp  AS Most Recent           FROM Messages          WHERE To ID   12345678          GROUP BY From ID          AS R     ON R To ID   M To ID AND R From ID   M From ID AND R Most Recent   M TimeStamp  WHERE M To ID   12345678   I ve added a filter on the To ID to match what you re likely to have   The query will work without it  but will return a lot more data in general   The condition should not need to be stated in both the nested query and the outer query  the optimizer should push the condition down automatically   but it can do no harm to repeat the condition as shown

User · Answer

this query return last record for every Form id       SELECT m1        FROM messages m1 LEFT JOIN messages m2      ON  m1 Form id   m2 Form id AND m1 id  lt  m2 id       WHERE m2 id IS NULL

User · Answer

You should find out last timestamp values in each group  subquery   and then join this subquery to the table -  SELECT t1   FROM messages t1   JOIN  SELECT from id  MAX timestamp  timestamp FROM messages GROUP BY from id  t2     ON t1 from id   t2 from id AND t1 timestamp   t2 timestamp

User · Answer

Try this  SELECT   FROM messages where id in  SELECT max id  FROM messages GROUP BY from id   order by id desc

User · Answer

Just complementing what Devart said  the below code is not ordering according to the question   SELECT t1   FROM messages t1   JOIN  SELECT from id  MAX timestamp  timestamp FROM messages GROUP BY from id  t2     ON t1 from id   t2 from id AND t1 timestamp   t2 timestamp    The  GROUP BY  clause must be in the main query since that we need first reorder the  SOURCE  to get the needed  grouping  so   SELECT t1   FROM messages t1   JOIN  SELECT from id  MAX timestamp  timestamp FROM messages ORDER BY timestamp DESC  t2     ON t1 from id   t2 from id AND t1 timestamp   t2 timestamp GROUP BY t2 timestamp    Regards

User · Answer

You need to order them   SELECT   FROM messages GROUP BY from id ORDER BY timestamp DESC LIMIT 1

[mysql] How to get the latest record in each group using GROUP BY?

Examples related to mysql

Examples related to sql

Examples related to greatest-n-per-group