Mysql get an order to happen before the group

Question

I have three tables and I will just show you the corresponding 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 without displaying duplicate groups.

I tried using the group as follows:

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 leads to a successful grouping, but runs before ORDER BY, so the first result is done before sorting.

Any help was appreciated.

Answer 1

, MAX(). .

...

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

Answer 2

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
        )

Answer 3

, :

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)