Can we Apply Group By on two columns in Sql Server 2017?

How to get last message of all users who did chat with UserID 1. I want to apply group by on two feilds. The Database is like:

MessageID    Message    MessageCreatorUserID     MessageRecieverUserID   CreatedAt

  1           HI                  4                         1              
  2           Hello               1                         3             
  3           Bye                 1                         3                       
  4           Thanks              1                         2
  5           hii                 1                         3

This is table and My question is:

UserID is stored in MessageCreatorUserID or MessageRecieverUserID

I want to get last message of all users, who did the chat with UserID 1.

Answer

Well, you can use window functions:

select t.*
from (select t.*,
             row_number() over (partition by (case when MessageCreatorUserId = 1 then MessageReceiverUserId else MessageCreatorUserId end)
                                order by messageId desc
                               ) as seqnum
      from t
      where 1 in (MessageCreatorUserId, MessageReceiverUserId)
     ) t
where seqnum = 1;