开发者

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
        )
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜