sql group by acting like facebook messaging (mssql sp)

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, 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