CODEWITHSUNDEEP
t-sql
Keith Sorbo
Keith Sorbo

Reputation: 25

Picking latest message from a conversation in a messages table

I have a messages table with structure something like this:

Messageid auto
FromUserID int
ToUserid Int
ConversationID int
Subject text
Message text
DateSent datetime
MessageRead bit

I need to write a query which return the row (or just the messageid and I can do a self-join) of the last (most recent) message for each conversation. Essentially this means that within a given conversation (represented by conversationid), which of several messages is the latest and what is the messageid of this message.

I can group by conversationid and ask for max(datesent), but then how do I get the messageid for that particular record?

(This is a production db, so I can't modify the table structures.)

Upvotes: 0

Views: 55

Answers (2)

Jonathan
Jonathan

Reputation: 83

Not sure if the execution time would be shorter than Paparazzi's... but here is an alternative you can try using an inner join:

select t.*
from table t
join (
         select conversationid, max(datesent)
         from table
         group by conversationid

      ) x on x.conversationid = t.conversationid and x.datesent = t.datesent

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

select * 
from 
( select * 
       , row_number() over (partition by ConversationID order by DateSent  desc) rn 
  from table 
) tt 
where tt.rn = 1

Upvotes: 1

Related Questions