1

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!

Charlieface
  • 52,284
  • 6
  • 19
  • 43
Jorge Zuverza
  • 885
  • 8
  • 26
  • 1
    You've tagged MySql but your syntax is clearly SQL Server. Review the exection plan to see how they compare. – Stu Jan 31 '22 at 19:03
  • 3
    Be very careful with your assumption. If you need the "last date", then you should order by that column and not assume ID and DATE are logically connected in the manner you assume. There are times (esp. during migrations) where there is a need to manipulate ID values (without affecting real "data) to achieve a particular goal . – SMor Jan 31 '22 at 19:05
  • 1
    Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – Charlieface Feb 01 '22 at 09:26
  • The two solutions you have will perform identically, as a correlated subquery is transformed into an `APPLY` anyway. You will see this from the execution plan. The benefit of the `APPLY` is that you can select multiple columns. There is also the `ROW_NUMBER` solution from the linked article – Charlieface Feb 01 '22 at 09:27

1 Answers1

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

Gene Kowalski
  • 21
  • 4