mysql get the order by to happen before the group by
I have three tables and I will just show you the relevant columns here
Table: groups Columns: group_id, name.
Table: groups_to_message Columns: group_id, message_id
Table: messages Columns: message_id, created (date)
I need to basically find the last message for each group, with out showing duplica开发者_运维百科te groups.
I've tried using group by like this:
SELECT m.created, g.group_id
FROM groupss as g
JOIN group_to_message as gm ON (g.group_id = gm.group_id)
JOIN messages as m
GROUP BY g.group_id
ORDER BY m.created DESC
This causes the successful grouping but is done before the ORDER BY so the first result is taken before the sort.
Any help appreciated.
If I'm reading your question right, Use MAX(). This should get you the most recent item for each group
Example...
SELECT MAX(m.created), g.group_id
FROM groupss as g
JOIN group_to_message as gm ON (g.group_id = gm.group_id)
JOIN messages as m ON (gm.message_id = gm.message_id)
GROUP BY g.group_id
ORDER BY m.created DESC
This is untested, but it should work:
SELECT m.created, g.group_id
FROM groups as g
JOIN group_to_message as gm ON (g.group_id = gm.group_id)
JOIN messages as m ON (gm.message_id = m.id)
GROUP BY g.group_id HAVING m.created = MAX(m.created)
SELECT m.created, g.group_id
FROM groupss g
JOIN messages m
ON m.id =
(
SELECT mi.id
FROM group_to_message gm
JOIN message mi
ON mi.message_id = gm.message_id
WHERE gm.group_id = g.group_id
ORDER BY
gm.group_id DESC, mi.created DESC
LIMIT 1
)
精彩评论