CODEWITHSUNDEEP
sqlsql-serverwhere-clause
Web Develop Wolf
Web Develop Wolf

Reputation: 6326

Last message sent logic issue

I'm attempting to identify the last message sent in a conversation for a set of conversations in SQL. I can get back the correct users and ID's no problem, but the last message and the time it was sent are coming back as the same for both conversations, whereas I need the last message sent in that particular conversation.

Here's the SQL I'm using:

SELECT ConversationId,
       (SELECT Username FROM dbo.[User] WHERE (UserId = dbo.Conversation.FromUser)) AS FromUser,
       (SELECT Username
        FROM dbo.[User] User_1
        WHERE (UserId = dbo.Conversation.ToUser)) AS ToUser,
       (SELECT TOP (1)
               MessageBody
        FROM dbo.ConversationMessage
        WHERE (ConversationId = ConversationId)
        ORDER BY MessageDateTime DESC) AS LastMessageBody,
       (SELECT TOP (1)
               CONVERT(varchar(5), MessageDateTime, 114) AS Expr1
        FROM dbo.ConversationMessage ConversationMessage_1
        WHERE (ConversationId = ConversationId)
        ORDER BY MessageDateTime DESC) AS LastMessageTime
FROM dbo.Conversation;

And here's the result set I'm getting back, just for clarity: enter image description here

LastMessageBody and LastMessageTime, should both be unique and since the ConversationId's are both unique I can't see why the same message should be returned each time. My understanding was that the individual row's ConversationId would be used in the where clause just like the UserId in the other columns?

Upvotes: 0

Views: 47

Answers (2)

Thom A
Thom A

Reputation: 95830

Honestly, this is a massive stab in the dark, however, it might be what you're after. There's no sample data or expected results here, so if this isn't what you need, then provide both.

I've also got rid of those awful subqueries and replaced them with JOINs:

SELECT TOP 1 WITH TIES
       C.ConversationId,
       Uf.Username AS FromUser,
       Ut.Username AS ToUser,
       CM.MessageBody AS LastMessageBody,
       CONVERT(time(0),CM.MessageDateTime) AS LastMessageTime
FROM dbo.[Conversation] C
     JOIN dbo.[User] Uf ON C.FromUser = Uf.UserId
     JOIN dbo.[User] Ut ON C.ToUser = Ut.UserId
     JOIN dbo.ConversationMessage CM ON C.ConversationId = CM.ConversationId
ORDER BY ROW_NUMBER() OVER (PARTITION BY C.ConversationId ORDER BY CM.MessageDateTime DESC);

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Whenever you have multiple tables in a query, always use table aliases and qualified column names. This is your problem.

Here is an example of how to fix it:

SELECT . . .
       (SELECT TOP (1) cm.MessageBody
        FROM dbo.ConversationMessage cm
        WHERE cm.ConversationId = c.ConversationId
        ORDER BY cm.MessageDateTime DESC
       ) AS LastMessageBody,
       . . .
 FROM dbo.Conversation c;

When all column names are properly qualified, your query should work as you intend.

Upvotes: 0

Related Questions