Get the last date in a one to many relationship in sql [closed]

Question

I need to get all the conversations from a user including the last date from a message. These are the tables:

Conversation table:

ConversationId	UserId	Subject
5	1	Help
6	2	No data in server
7	1	Server Help

Message table:

MessageId	ConversationId	Text	Created
1	5	Error in..	2/2/2020
2	5	Need help…	2/3/2020
3	5	successfully..	2/4/2020
4	6	Help	2/5/2020
5	7	server not working	2/6/2020

My result for the conversations for user 1 would be this:

ConversationId	UserId	Subject	LastCreatedMessageDate
5	1	Help	2/4/2020
7	1	Server Help	2/6/2020

My first option is to do a subquery like:

SELECT
    c.conversationId,  
    c.userid,  
    c.subject,
    (SELECT TOP 1 m.Created
     FROM Message as m 
     WHERE m.conversationId  = c.conversationId
     ORDER BY MessageId DESC) AS LastCreatedMessageDate 
FROM
    conversation c 
WHERE 
    c.userid = '1'

Second option would be to use outer apply like:

SELECT
    c.conversationId,  
    c.userid,  
    c.subject,
    m.lastCreatedMessage
FROM
    conversation c 
OUTER APPLY 
    (SELECT TOP 1 m.Created AS lastCreatedMessage
     FROM Message m 
     WHERE m.conversationId  = c.conversationId
     ORDER BY MessageId DESC) m
WHERE 
    c.userid = '1'

What would be a better way of joining these two tables? Is one query any better than the other? Is there a better option of getting the data maybe with joins?

Thanks!

Answer 1

Academically, they look identical to me. I would go with the first one for readability and I don't see a better way to write it.

If you are worried about speed, compare the execution plan of each but they will both be instantaneous with 5 rows