Sql Group By Acting Like Facebook Messaging (mssql Sp)

im trying to group messages like conversations, user1 is @user (logged in user) UserName, UserFullName is always non logged in user (who the conversation is with) Message, Da

Solution 1:

The answer is similar to your earlier question. However, now, it must take into account that the @user could be either user in the message.

In this case, row_number() is not directly of help.

Here are the differences. There is now a subquery to put the two users in "canonical" order. So, all messages between them have the same User1 and User2 (based on alphabetical order).

The partition by clause uses these columns, so all messages are included in the seqnum calculation. The Users table now fetches information about the current user directly.

select FromUser, ToUser, Message, [Date], UserId, UserFullName, UserName, UserPhoto
from (SELECT CM.FromUser, CM.ToUser, CM.Message, CM.[Date], U.UserId,
             U.UserFullName, U.UserName, U.UserPhoto,
             row_number() over (partitionby CM.User1, CM.User2
                                orderby CM.[Date] desc) as seqnum
      FROM (select CM.*,
                   (casewhen FromUser < ToUser then FromUser else ToUser end) as User1,
                   (casewhen FromUser < ToUser then ToUser else FromUser end) as User2
            from ConversationMessages CM
           ) CM CROSSJOIN
           (select*from Users U
            where@user= u.UserName
           ) U
      WHERE@userin (CM.ToUser, CM.FromUser)
     ) s
WHERE seqnum =1ORDERBY s.[Date] DESC ;

EDIT:

The above returns the user information for @user. For the other participant:

select FromUser, ToUser, Message, [Date], UserId, UserFullName, UserName, UserPhoto
from (SELECT CM.FromUser, CM.ToUser, CM.Message, CM.[Date], U.UserId,
             U.UserFullName, U.UserName, U.UserPhoto,
             row_number() over (partitionby CM.User1, CM.User2
                                orderby CM.[Date] desc) as seqnum
      FROM (select CM.*,
                   (casewhen FromUser < ToUser then FromUser else ToUser end) as User1,
                   (casewhen FromUser < ToUser then ToUser else FromUser end) as User2
            from ConversationMessages CM
           ) CM JOIN
           Users U
           on U.UserName <>@userand
              U.UserName in (CM.FromUser, CM.ToUser)
      WHERE@userin (CM.ToUser, CM.FromUser)
     ) s
WHERE seqnum =1ORDERBY s.[Date] DESC ;