Patriotec Patriotec - 6 months ago 13
SQL Question

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

Using MS SQL Server 2014. I need to select the row where (userid=1 or memberid=1) that has the max(messageid) value from all the messages where the user #1 sent or received messages ordered by messageid desc

I tried the solution here: How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?, but since a user can send OR receive messages, the solution only partly solves my 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 I tried

DECLARE @userid bigint

SET @userid=1

SELECT mm.*
FROM messages mm
INNER JOIN
(SELECT memberid AS otherperson, MAX(m.messageid) AS MaxID
FROM messages m
WHERE m.userid=@userid
GROUP BY memberid
) groupedmm
ON mm.memberid = groupedmm.otherperson
AND mm.messageid = groupedmm.MaxID

UNION

SELECT mmm.*
FROM messages mmm
INNER JOIN
(SELECT userid AS otherperson, MAX(m.messageid) AS MaxID
FROM messages m
WHERE m.memberid=@userid
GROUP BY userid
) groupedmmm
ON mmm.userid = groupedmmm.otherperson
AND 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

Answer

This seems to do the job - quite straightforward if you use a 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 into @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 as (
    select *,
        ROW_NUMBER() OVER (PARTITION BY
           CASE WHEN userid = @user_id THEN memberid else userid END
           ORDER BY created desc) rn
    from @t
    where
        userid = @user_id or
        memberid = @user_id
)
select * from Ordered where 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 the use of the CASE expression to derive the PARTITION value as, essentially, "whichever column wasn't matched in the WHERE clause". This can be trickier to extend to more than two columns.