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

Question

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

So, the input is an appropriately set `@userid` variable and the required output is the *entire row* that represents the latest message (rather than, as you specify/imply early in the question where you say you're just after the highest `messageid`) — Damien_The_Unbeliever, May 09 '16 at 09:23
Yes, the latest message, but with userid=1 or memberid=1. Hmmm, think of it like how messages on Facebook is arranged — Patriotec, May 09 '16 at 09:31

score 3 · Accepted Answer · answered May 09 '16 at 09:30

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.

Thanks. You improved performance also: Table 'messages'. Scan count 1, logical reads 3. I was at: Scan count 4, logical reads 12 and was only half done — Patriotec, May 09 '16 at 09:40

score 1 · Answer 2 · answered May 09 '16 at 09:46

 CREATE TABLE #D
(
MESSAGEID INT,  USERID INT, MEMBERID INT,  MESSAGE VARCHAR(10),  CREATED DATETIME)
INSERT INTO #D VALUES


(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')  



 SELECT TT.*
    FROM #D TT
    INNER JOIN
        (SELECT USERID, MAX(CREATED) AS MAXDATETIME
        FROM #D
        GROUP BY USERID) GROUPEDTT 
    ON TT.USERID = GROUPEDTT.USERID 
    AND TT.CREATED = GROUPEDTT.MAXDATETIME

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

2 Answers2