14

----Section 3: Querying - 14. Last Chat

DECLARE @LastChatId INT = (SELECT TOP (1) c.Id FROM Chats AS c
                            ORDER BY c.StartDate DESC)

DECLARE @FirstMessage VARCHAR(200) =
                                    (
                                        SELECT TOP (1) m.Content
                                        FROM Messages AS m
                                        WHERE m.ChatId = @LastChatId
                                        ORDER BY m.SentOn
                                    )


SELECT c.Title, @FirstMessage AS [Content]
FROM Chats AS c
WHERE c.Id = @LastChatId
--2/4 IN JUDGE



SELECT TOP (1) c.Title, m.Content
FROM Chats AS c
LEFT JOIN Messages AS m
ON m.ChatId = c.Id
ORDER BY c.StartDate DESC
--2/4 IN JUDGE



SELECT c.Title , m.Content
FROM Chats c
LEFT JOIN Messages m ON m.ChatId = c.Id
WHERE c.StartDate = (SELECT MAX(StartDate) FROM Chats)
--4/4 IN JUDGE