sql server - SELECT MAX(column) AND DISTINCT by one of two columns in MS SQL -

using ms sql server 2014. need select row (userid=1 or memberid=1) has max(messageid) value messages user #1 sent or received messages ordered messageid desc

i tried solution here: how can select rows max(column value), distinct column in sql?, since user can send or receive messages, solution partly solves problem.

message table

messageid  userid  memberid  message  created -------------------------------------------------------------- 9          4       1         hi 9     2016-05-09 01:50:59.423  8          4       1         hi 8     2016-05-09 01:50:43.950 7          1       4         hi 7     2016-05-09 01:50:35.310 6          1       4         hi 6     2016-05-09 01:50:25.887 5          1       2         hi 5     2016-05-08 23:49:41.610 11         2       1         hi 11    2016-05-09 03:26:42.267 12         1       3         hi 12    2016-05-09 05:06:11.030 1          1       2         hi 1     2016-05-08 22:37:57.803 

expected result

messageid  userid  memberid  message  created -------------------------------------------------------------- 12         1       3         hi 12    2016-05-09 05:06:11.030 11         2       1         hi 11    2016-05-09 03:26:42.267 9          4       1         hi 9     2016-05-09 01:50:59.423  

query tried

declare @userid bigint  set @userid=1  select mm.* messages mm inner join     (select memberid otherperson, max(m.messageid) maxid     messages m     m.userid=@userid     group memberid        ) groupedmm  on mm.memberid = groupedmm.otherperson  , mm.messageid = groupedmm.maxid  union  select mmm.* messages mmm inner join     (select userid otherperson, max(m.messageid) maxid     messages m     m.memberid=@userid     group userid      ) groupedmmm  on mmm.userid = groupedmmm.otherperson  , mmm.messageid = groupedmmm.maxid 

above query returned

messageid  userid  memberid  message  created -------------------------------------------------------------- 5          1       2         hi 5     2016-05-08 23:49:41.610 7          1       4         hi 7     2016-05-09 01:50:35.310 9          4       1         hi 9     2016-05-09 01:50:59.423 11         2       1         hi 11    2016-05-09 03:26:42.267  12         1       3         hi 12    2016-05-09 05:06:11.030 

this seems job - quite straightforward if use partitioned function:

declare @user_id int set @user_id = 1 declare @t table (messageid tinyint, userid tinyint, memberid tinyint, message varchar(17),                   created datetime) insert @t(messageid,  userid,  memberid,  message,  created) values (9          ,4       ,1         ,'hi 9'     ,'2016-05-09t01:50:59.423'), (8          ,4       ,1         ,'hi 8'     ,'2016-05-09t01:50:43.950'), (7          ,1       ,4         ,'hi 7'     ,'2016-05-09t01:50:35.310'), (6          ,1       ,4         ,'hi 6'     ,'2016-05-09t01:50:25.887'), (5          ,1       ,2         ,'hi 5'     ,'2016-05-08t23:49:41.610'), (11         ,2       ,1         ,'hi 11'    ,'2016-05-09t03:26:42.267'), (12         ,1       ,3         ,'hi 12'    ,'2016-05-09t05:06:11.030'), (1          ,1       ,2         ,'hi 1'     ,'2016-05-08t22:37:57.803')  ;with ordered (     select *,         row_number() on (partition            case when userid = @user_id memberid else userid end            order created desc) rn     @t             userid = @user_id or         memberid = @user_id ) select * ordered rn = 1 

results:

messageid userid memberid message           created                 rn --------- ------ -------- ----------------- ----------------------- -------------------- 11        2      1        hi 11             2016-05-09 03:26:42.267 1 12        1      3        hi 12             2016-05-09 05:06:11.030 1 9         4      1        hi 9              2016-05-09 01:50:59.423 1 

note use of case expression derive partition value as, essentially, "whichever column wasn't matched in where clause". can trickier extend more 2 columns.