sql group by acting like facebook messaging (mssql

Question

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, Date will be whoever has the last message

example 1: 
FromUser | ToUser | Message | Date
User2    | User1  | hi      | 01/01/2013 20:00
User1    | User2  | hi back | 01/01/2013 21:00

userfullname and username will be from touser (non logged)
message and date from fromuser (logged in @user, as last message in group)

example 2: 
FromUser | ToUser | Message | Date
User1    | User2  | hi      | 01/01/2013 20:00
User2    | User1  | hi back | 01/01/2013 21:00

userfullname and username will be from fromuser (non logged in)
message and date from fromuser (logged in @user as its the last message in group)

This will show just like facebook conversations, if any of you have used their messaging system. thanks all! :) fries my brain just thinking about sql

SELECT        
CM.FromUser, CM.ToUser, CM.Message, CM.Date, 
U.UserId, U.UserFullName, U.UserName, U.UserPhoto
FROM
ConversationMessages AS CM 
INNER JOIN
Users AS U ON U.UserName = CM.FromUser
WHERE
CM.ToUser = @user
ORDER BY 
CM.Date DESC 

Answer 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 (partition by CM.User1, CM.User2
                                order by CM.[Date] desc) as seqnum
      FROM (select CM.*,
                   (case when FromUser < ToUser then FromUser else ToUser end) as User1,
                   (case when FromUser < ToUser then ToUser else FromUser end) as User2
            from ConversationMessages CM
           ) CM CROSS JOIN
           (select *
            from Users U
            where @user = u.UserName
           ) U
      WHERE @user in (CM.ToUser, CM.FromUser)
     ) s
WHERE seqnum = 1
ORDER BY 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 (partition by CM.User1, CM.User2
                                order by CM.[Date] desc) as seqnum
      FROM (select CM.*,
                   (case when FromUser < ToUser then FromUser else ToUser end) as User1,
                   (case when FromUser < ToUser then ToUser else FromUser end) as User2
            from ConversationMessages CM
           ) CM JOIN
           Users U
           on U.UserName <> @user and
              U.UserName in (CM.FromUser, CM.ToUser)
      WHERE @user in (CM.ToUser, CM.FromUser)
     ) s
WHERE seqnum = 1
ORDER BY s.[Date] DESC ;