First order by, then Group by

Question

I have a table with messages (person_id, message, conversation_id, created_at)

I want to get the newest message on each conversation.. I have tried with a subquery, but it seems to ignore my orderby in the subquery:

SELECT sub.* FROM 
      (SELECT * FROM messages ORDER BY created_at DESC) AS sub
GROUP BY sub.conversation_id

Try it in this SQLfiddle.

Any other way to get the right result ?

http://sqlfiddle.com/#!9/12739/1

Possible duplicate of [mysql-order-by-before-group-by](http://stackoverflow.com/questions/14770671/mysql-order-by-before-group-by) — BJones, Jun 14 '16 at 15:49

score 4 · Accepted Answer · answered Jun 14 '16 at 16:27

4

If you want all the laste messages for each conversation you can use a where in on a select group by

SELECT messages.* 
FROM messages 
WHERE (messages.created_at, messages.conversation_id) in 
  (SELECT max(created_at) , conversation_id
     FROM messages messages
     Group by conversation_id);

answered Jun 14 '16 at 16:27

ScaisEdge

131,976
10
91
107

Thank you ! Is this the most optimized way ? – Thomas Bolander Jun 15 '16 at 07:59
1

the alternative is a inner join on the same table grouped by .. probably the query in asnwer is the most optimized ..but the result could depend on the real population of the table .. – ScaisEdge Jun 15 '16 at 15:36

score 1 · Answer 2 · answered Jun 14 '16 at 15:49

1

Try this way:

SELECT m1.*
FROM messages AS m1
JOIN (
   SELECT conversation_id, MAX(created_at) AS created_at
   FROM messages
   GROUP BY conversation_id
) AS m2 ON m1.conversation_id = m2.conversation_id AND m1.created_at = m2.created_at

answered Jun 14 '16 at 15:49

Giorgos Betsos

71,379
9
63
98

score 1 · Answer 3 · answered Jun 14 '16 at 15:49

1

Couldn't test this on sqlfiddle, it's not working for me:

SELECT m1.* 
  FROM messages m1
 WHERE m1.created_at =
      (SELECT max(created_at) 
         FROM messages m2
        where m1.conversation_id=m2.conversation_id)

answered Jun 14 '16 at 15:49

vercelli

4,717
2
13
15

score 1 · Answer 4 · answered Jun 14 '16 at 15:56

You can either do this with a correlated subquery in the select list, or with a subquery in the from list joined back to you main table.

Correlated subquery:

SELECT DISTINCT m1.conversation_id, (SELECT * FROM messages m2 WHERE m2.conversation_id=m1.conversation_id ORDER BY created_at DESC LIMIT 1)
FROM messages m1

Subquery in the from clause:

SELECT m1.*
FROM messages as m1
JOIN (
   SELECT conversation_id, max(created_at) AS max_created_at
   FROM messages
   GROUP BY conversation_id
) m2 on m1.conversation_id = m2.conversation_id and m1.created_at = m2.max_created_at

The difference between the 2 approach is that the correlated subquery will always return 1 records per conversation id. The second one may return more than 1 record per conversation, if 2 records have the same created_at value. If there is an auto incremented id field identifying each message uniquely, then you can use the max of this field instead of the created at field to go around this potential issue.

First order by, then Group by

4 Answers4