CzarJohn Demafeliz CzarJohn Demafeliz - 5 months ago 15
SQL Question

Selecting latest message per thread

I have a table (messages) with the following columns

message_id (pk), thread_id, message_body, date_posted, posted_by,....

How can I select the latest message per thread with the results in descending order according to date_posted?

sample table

-------------------------------------------------
message_id | thread_id | body | date_posted
-------------------------------------------------
1 | 1 | ... | 2016-06-03
-------------------------------------------------
2 | 1 | ... | 2016-06-04
-------------------------------------------------
3 | 2 | ... | 2016-06-05
-------------------------------------------------
4 | 1 | ... | 2016-06-06
-------------------------------------------------
5 | 2 | ... | 2016-06-07
-------------------------------------------------
6 | 3 | ... | 2016-06-08
-------------------------------------------------
7 | 2 | ... | 2016-06-09
-------------------------------------------------


expected result

-------------------------------------------------
message_id | thread_id | body | date_posted
-------------------------------------------------
7 | 2 | ... | 2016-06-09
-------------------------------------------------
6 | 3 | ... | 2016-06-08
-------------------------------------------------
4 | 1 | ... | 2016-06-06
-------------------------------------------------

Answer

Try this;)

select t1.*
from messages t1
inner join (
    select max(date_posted) as date_posted, thread_id
    from messages
    group by thread_id
) t2 on t2.thread_id = t1.thread_id and t2.date_posted = t1.date_posted
order by t1.date_posted

Or you can use in :

select *
from messages
where (date_posted, thread_id) in (
    select max(date_posted) as date_posted, thread_id
    from messages
    group by thread_id
)
order by date_posted

SQLFiddle DEMO HERE

Comments