Hi,
Please refer below code.
SQL
DECLARE @users AS TABLE (ID INT,Sender VARCHAR(10),Recipient VARCHAR(10),[Message] VARCHAR(100),[DateTime] DATETIME)
INSERT INTO @users VALUES(1,'a','b','Hi','5/2/16 01:01:20')
INSERT INTO @users VALUES(2,'b','a','Hello','5/4/16 01:01:20')
INSERT INTO @users VALUES(3,'c','a','Gud day','5/4/16 01:01:20')
INSERT INTO @users VALUES(4,'a','b','Thanks','5/5/16 01:01:20')
INSERT INTO @users VALUES(5,'a','c','Wlcm','5/5/16 01:01:20')
INSERT INTO @users VALUES(6,'c','a','My Pleasure','5/6/16 01:01:20')
DECLARE @User VARCHAR(1)
SET @User = 'a'
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) as RowNumber,* INTO #temp FROM (
SELECT sender FROM @users WHERE Recipient = @User GROUP BY sender
UNION
SELECT Recipient FROM @users WHERE Sender = @User GROUP BY Recipient
) as tbl
CREATE TABLE #Result(ID INT,Sender VARCHAR(10),Recipient VARCHAR(10),[Message] VARCHAR(100),[DateTime] DATETIME)
DECLARE @i INT
SET @i = 0
WHILE @i <= (SELECT COUNT(*) FROM #temp)
BEGIN
DECLARE @u VARCHAR(10)
SELECT @u = sender FROM #temp WHERE RowNumber = @i
INSERT INTO #Result
SELECT TOP 1 * FROM @users WHERE Sender = @u OR Recipient = @u ORDER BY datetime DESC
SET @i= @i + 1
END
SELECT * FROM #Result
DROP TABlE #temp
DROP TABlE #Result
Screenshot
