CODEWITHSUNDEEP
mysqlsql
CzarJohn Demafeliz
CzarJohn Demafeliz

Reputation: 328

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
-------------------------------------------------

Upvotes: 1

Views: 58

Answers (4)

Ullas
Ullas

Reputation: 11556

Just an other perspective by giving a row number based on the thread_id and date_posted in descending order.

Query

select t.message_id, t.thread_id, t.body, t.date_posted from( 
    select message_id, thread_id, body, date_posted, 
    (
        case thread_id when @curA 
        then @curRow := @curRow + 1 
        else @curRow := 1 and @curA := thread_id end 
    ) as rn 
    from messages m, 
    (select @curRow := 0, @curA := '') r 
    order by thread_id, date_posted desc
)t
where t.rn = 1
order by t.date_posted desc;

SQL Fiddle Demo

Upvotes: 0

Blank
Blank

Reputation: 12378

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

Upvotes: 3

Mahesh Madushanka
Mahesh Madushanka

Reputation: 2998

you can do it like this 

SELECT thread_id,message FROM  (Select *  from messages   ORDER BY thread_id,latestDate DESC) r group by thread_id;

Upvotes: 1

Wasiq Muhammad
Wasiq Muhammad

Reputation: 3118

Try this

SELECT * FROM messages GROUP BY thread_id ORDER BY  date_posted DESC;

Upvotes: 0

Related Questions