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.

There's even a better solution to this problem [here](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) — phil pirozhkov, Oct 20 '14 at 15:48

score 119 · Accepted Answer · answered Jun 12 '12 at 15:18

119

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;

answered Jun 12 '12 at 15:18

Devart

119,203
23
166
186

7

+1 A common problem but thank you so much for not using MySQLs feature of allowing columns in the select that are not in the group by! – GarethD Jun 12 '12 at 15:29
Thank you SOOO much! I was able to join additional table with no problem. Such a good approach. – user1019144 Jun 12 '12 at 15:37
This is just perfect. – enchance Nov 24 '13 at 17:59
1

For those not used to SQL (like me). If I am not wrong, 't1', 't2' and 'timestamp' are ALIASES, so t2 is the result of the inner SELECT, and t2.timestamp targets the MAX(timestamp) column, so you can use it from the outer SELECT in the ON statement. Maybe adding some "AS" could be a good help for rookies like me :) – Dani bISHOP Jan 13 '14 at 17:24
I didn't like it, I really wanted to do an inner join onto myself instead of an implicit subquery; but the subquery seems to be the only way. – ThorSummoner Jul 30 '14 at 22:12
1

This query will be slow if messages table become big. Better use method from documentation page: http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html – vvolkov Oct 24 '16 at 08:29

score 40 · Answer 2 · edited Mar 04 '23 at 14:55

40

Try this

SELECT * FROM messages where id in (SELECT max(id) FROM messages GROUP BY from_id ) order by id desc

"SELECT max(id) FROM messages GROUP BY from_id" this inner query, it first grouping records/messages by user(from_id) then pulling max record id. Then we are querying again on messages table to get only latest records/messages from inner query result set.

edited Mar 04 '23 at 14:55

random

9,774
10
66
83

answered Apr 21 '16 at 18:27

Venu Morigadi

589
6
4

6

While this code may answer the question, it would be better to include some _context_, explaining _how_ it works and _when_ to use it. Code-only answers are not useful in the long run. – Benjamin W. Apr 21 '16 at 18:48
"SELECT max(id) FROM messages GROUP BY from_id" this inner query,it first grouping records/messages by user(from_id) then pulling max record id. Then we are querying again on messages table to get only latest records/messages from inner query result set. – Venu Morigadi Apr 25 '16 at 04:12
The simplest solution IMHO – Nowdeen May 22 '16 at 15:36
3

This may not work if you want to get the record having max value based on some other non-primary key column. – Vivek Giri Mar 08 '17 at 12:25
This is the only workaround for me – wdetac Jun 08 '17 at 10:54
Awesome! Simple and does the job. – kiradotee Dec 09 '18 at 23:29

score 26 · Answer 3 · answered Aug 10 '17 at 07:22

26

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 < m2.id)
     WHERE m2.id IS NULL;

answered Aug 10 '17 at 07:22

leyla azari

913
11
20

2

Honestly this answer is underappreciated. This was the only solution that worked well for me since i am grouping by a different field besides my auto increment field, and I have to select the latest by date. – Samuel Willems Oct 28 '17 at 13:29
1

This answer helped me to get it working with Hibernate HQL. None of the other answers worked, because Hibernate supports subqueries only after WHERE and SELECT. Since this answer doesn't use any subqueries at all, it worked just fine. – Alex Nov 22 '17 at 11:16

score 2 · Answer 4 · answered Jun 12 '12 at 15:18

2

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.

answered Jun 12 '12 at 15:18

Jonathan Leffler

730,956
141
904
1,278

The latest Standard SQL allows you to omit columns in the `GROUP BY` and include them in the `SELECT` or `HAVING` clause, provided they are functionally dependant on the `GROUP BY` combination - and thus only deterministic results would be returned. (MySQL does no such check of course.) – ypercubeᵀᴹ Jun 12 '12 at 15:32
@ypercube Not sure if this is the place for this, but do you have any good links for this. I cannot get my head around how selecting columns that are not in the group by could become deterministic by being dependant on items in the group by, the only way I can see to make it non deterministic is through the use of order by. However seeing examples may help clear things up. Thanks – GarethD Jun 12 '12 at 15:52
`GROUP BY pk` would be a simple example. My answer [here](http://stackoverflow.com/questions/7594865/why-does-mysql-add-a-feature-that-conflicts-with-sql-standards/7596265#7596265) has link to (a copy of) the standard. – ypercubeᵀᴹ Jun 12 '12 at 16:02

score 2 · Answer 5 · answered Sep 15 '15 at 13:53

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,

score -5 · Answer 6 · answered Jun 12 '12 at 15:12

-5

You need to order them.

SELECT * FROM messages GROUP BY from_id ORDER BY timestamp DESC LIMIT 1

answered Jun 12 '12 at 15:12

Marcus Recck

5,075
2
16
26

@BumbleB2na `LIMIT 1` you mean? – Li0liQ Jun 12 '12 at 15:14
now you'll get the last message of ALL THE MESSAGES, you need a where condition – jcho360 Jun 12 '12 at 15:15
oops mysql not sql... you're right – BumbleB2na Jun 12 '12 at 15:18
1

@BumbleB2na: Actually it is SQL too, only it's MySQL SQL, not SQL Server SQL (aka Transact-SQL). :) – Andriy M Jun 12 '12 at 15:19
This still gives me the older message – user1019144 Jun 12 '12 at 15:23
The limit one is not required, this is done by the `group by from_id` which will limit the output to one row per from_id – GarethD Jun 12 '12 at 15:30
1

It will give you same result just that the obtained result will be sorted by timestamp. – Abhishek Goel Sep 29 '15 at 08:57

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

6 Answers6

Linked

Related