mysql join table select with and without GROUP BY

I have the following two tables:

CREATE TABLE messages (
    messageId INT,
    threadId INT,
    recipientUserId INT,
    message TEXT
);

CREATE TABLE thread_recipients (
    recipientUserId INT,
    threadId INT
);

I want to SELECT a specific user's most recent message for each thread from the message table. I also want to get all the other users that are 开发者_运维问答part of the selected threads from the thread_recipients table.

I thought the following would work:

SELECT MAX(m.messageId) AS maxMessageId, m.threadId, m.message 
  FROM messages AS m
    RIGHT JOIN thread_recipients AS tr ON tr.threadId=m.threadId
  WHERE m.recipientUserId='2'
  GROUP BY m.threadId

However, due to the GROUP BY it only selects one row for each threadId, regardless of the number of users that are part of that thread. Is it even possible to do in one query?

Kind regards to any responders.

Oracle solves this simply by using analytic functions:

select max(messageId) over (partition by threadId) ...

very convenient for calling aggregate functions without a group by clause and fits perfectly for your case.

In MYSQL however, use an inner select to mimic this, but bear in mind they can be heavy on the memory for very large tables.

SELECT mm.maxMessageId, m.threadId, m.message 
FROM messages AS m RIGHT JOIN thread_recipients AS tr ON tr.threadId=m.threadId, 
(
   select threadId, max(messageId) as maxMessageId
   from messages group by threadId
) AS mm
WHERE m.recipientUserId=2
  AND m.threadId = mm.threadId