0

Given this table:

table messages

id | conversation_id | user_id | message | created_at
-----------------------------------------------------
1  |       1         |   1     |   hello | 2017-05-09 
2  |       1         |   2     |   hi    | 2017-05-10
3  |       2         |   2     |   me    | 2017-05-11 
4  |       2         |   3     |   oh    | 2017-05-12 
5  |       1         |   1     |   okay  | 2017-05-13

I want to get the list of all "first" messages, so my expected result is

id | conversation_id | user_id | message | created_at
-----------------------------------------------------
1  |       1         |   1     |   hello | 2017-05-09 
3  |       2         |   2     |   me    | 2017-05-11

Currently I have this query:

select * from messages group by conversation_id ORDER BY  created_at desc

however this will return:

id | conversation_id | user_id | message | created_at
-----------------------------------------------------
5  |       1         |   1     |   okay  | 2017-05-13 
4  |       2         |   3     |   oh    | 2017-05-12

which is the last messages and they are ordered by their date.

How can I retrieve the data I wanted?

I am L
  • 4,288
  • 6
  • 32
  • 49
  • `SELECT a.* FROM messages a JOIN (SELECT MIN(id) id FROM messages GROUP BY conversation_id) b ON b.id = a.id;` – Strawberry Aug 22 '17 at 14:19

1 Answers1

4

Here is one way to get first messages:

select m.*
from messages m
where m.created_at = (select min(m2.created_at) from messages m2 where m2.conversation_id = m.conversation_id);

GROUP BY is not appropriate for this query. You are not aggregating anything.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786