This is my table.
SELECT * FROM [Message]
Now what I want is, I want the list of only last message which User with Id: 101 has sent or received from any other user. The query which I wrote for it is below
SELECT
(SELECT TOP 1 [Message_id]
FROM [Message]
WHERE
([Sender_id] = REC.[Sender_id] AND [Receiver_id] = REC.[Receiver_id])
OR
([Sender_id] = REC.[Receiver_id] AND [Receiver_id] = REC.[Sender_id])
ORDER BY 1 DESC) AS [Message Id],
REC.[Sender_id] AS [Sender Id],
REC.[Receiver_id] AS [Receiver Id],
(SELECT TOP 1 [Message]
FROM [Message]
WHERE
([Sender_id] = REC.[Sender_id] AND [Receiver_id] = REC.[Receiver_id])
OR
([Sender_id] = REC.[Receiver_id] AND [Receiver_id] = REC.[Sender_id])
ORDER BY 1 DESC) AS [Message]
FROM
(SELECT DISTINCT [Sender_id], [Receiver_id]
FROM [Message]
WHERE [Sender_id] = '101') REC
And I am getting the following result which seems fine.
I'm new to DB queries and it seems that my query is very inefficient and long. Can anyone please suggest a better way to write this query? Also, using JOINS if that might be a better possible way to write this query.
Thanks.
Aucun commentaire:
Enregistrer un commentaire