Reputation: 455
I have these tables.
User:
|ID|Field1....|
UserMessages:
|ID|UserSenderId|UserReceiverId|Message|CreatedOn|
Where UserSenderId
and UserReceiverId
are FK of PK User.Id
I would like to select the first message of each conversation between users (like Facebook where on the left I see a list of users and the first chars of the last message)
I tried with this query but I have multiple results for the same couple of users
SELECT DISTINCT
UserMessages.*
FROM
UserMessages
JOIN
(SELECT
MAX(UserMessages.Id) AS IDMAX,
UserMessages.IdReceiver,
UserMessages.IdSender
FROM
UserMessages
WHERE
UserMessages.IdReceiver = @UserId OR UserMessages.IdSender = @UserId
GROUP BY
UserMessages.IdReceiver, UserMessages.IdSender) IDMAX ON IDMax.IDMAX = UserMessages.Id
Example
UserMessages
:
|ID|UserSenderId|UserReceiverId|Message|
1|46|47|Hello
2|47|46|Hello!
3|46|48|Hey!!
4|50|46|How are you?
5|51|49| Hello
Considering logged userid = 46, I would like to select only the messages with id 2 3 4
, where id = 2 is the last one between user 46 And 47 (logged user could be sender or receiver)
Thanks!
Upvotes: 0
Views: 797
Reputation: 1271241
You can do this with row_number()
. The trick is to pair messages regardless of who the sender and receiver are. You can do this by putting the smaller id first, then the larger id:
select um.*
from (SELECT um.*,
row_number() over (partition by (case when idReceiver < idSender then idReceiver else idSender end),
(case when idReceiver < idSender then idSender else idReceiver end)
order by id desc
) as seqnum
from UserMessages um
where um.IdReceiver = @UserId OR um.IdSender = @UserId
) um
where seqnum = 1;
Upvotes: 2