CODEWITHSUNDEEP
sqlsql-servert-sql
Ertan Hasani
Ertan Hasani

Reputation: 817

How to get only last messages for each conversation in MSSQL

I am trying to make a simple messenger app when you can talk with other users, but I’m running on some issue while trying to create the query in MSSQL of getting all conversations with other user but with only last message so it will show to user before deciding in which conversation he wants to go in and read all the messages of it.

I have a table like this

ID      FromUserId      ToUserId        Message             SentDate                        Seen
1           32              35        HEY               2018-01-01 01:02:01         False
2           35              32        How are you       2018-01-01 01:02:07         False
3           32              36        HELLO             2018-01-01 17:00:22         False
4           37              32        Hey Buddy         2018-01-01 17:05:22         False

And the result I want to get it will looks like this:

ID      FromUserId      ToUserId        Message             SentDate                        Seen
4           37              32        Hey Buddy         2018-01-01 17:05:22         False
3           32              36        HELLO             2018-01-01 17:00:22         False
2           35              32        How are you       2018-01-01 01:02:07         False

So the last conversation was with user 37 with last message Hey Buddy at 2018-01-01 17:05:22 Then with user 36 with HELLO at 2018-01-01 17:00:22 Then with user 37 with last message How are you at 2018-01-01 01:02:03

I need something like when I pass userid in query, it will check for all messages of wheather userid is in FromUserId or ToUserId.

Upvotes: 0

Views: 194

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Use row_number() function :

select t.*
from (select t.*, row_number() over (partition by FromUserId order by SentDate desc) as seq
      from table t
     ) t
where seq = 1;

Upvotes: 2

Fahmi
Fahmi

Reputation: 37473

Use row_number()

select * from 
(select ID,FromUserId ,ToUserId,Message,SentDate,Seen,
row_number() over (partition by FromUserId order by sentdate desc) as rn
from tablename) a where rn=1

Upvotes: 1

Related Questions