user4756836 user4756836 - 1 month ago 7
MySQL Question

return last message from grouped messages SQL

I have two sql tables one which has users and the other which has messages.

Right now my query is:

SELECT messages.*, username FROM messages
JOIN users ON messages.from_ID = users.user_ID
WHERE to_ID = 19
GROUP BY thread_ID
ORDER BY msg_ID DESC;


Entity(messages):

msg_ID
from_ID
to_ID
subject
message
date


Right now, it is returning FIRST message where
to_ID = 19
. What I want to do is show the LATEST message ordered by the highest
msg_ID
. I can do a
order by
on it but my sql statement only returns one row so I have to do something before it reaches the
group by
statement.

If these are the two rows eligible to be returned:

msg_ID from_ID to_ID subject message date thread_id username
15 26 19 Hey testing string2 1477750565 1 testing
17 26 19 Hey testing string. 1477750594 1 testing


My current result:

msg_ID from_ID to_ID subject message date thread_id username
15 26 19 Hey testing string2 1477750565 1 testing


What I want:

msg_ID from_ID to_ID subject message date thread_id username
17 26 19 Hey testing string. 1477750594 1 testing // msg_ID is higher here

Answer

You should do this by selecting the correct message, not by using aggregation. Here is one method:

SELECT m.*, u.username
FROM messages m JOIN
     users u
     ON m.from_ID = u.user_ID
WHERE m.to_ID = 19 AND
      m.msg_id = (SELECT MAX(m2.msg_id)
                  FROM messages m2
                  WHERE m2.thread_id = m.thread_id AND m2.to_ID = m.to_ID
                 )
ORDER BY msg_ID DESC;