How to sort records before grouping?

Question

MySQL

I need to:
1. Count the number of new messages
2. Group by sender
3. Display in descending order. From newer to older.

The problem is that it is not possible to sort in descending order before grouping. And after grouping, it no longer makes sense, because. in the grouping, the first value with the date is taken.

Tried to sort with a nested query something like this:

SELECT `r`.`room`, COUNT(`m`.`id`), `m`.`date` 
FROM (SELECT * FROM `messenger` ORDER BY `date` DESC) AS `m`
JOIN `rooms` AS `r` ON `m`.`id_room` = `r`.`id` 
GROUP BY (`m`.`id_room`)

Answer 1

I solved the issue with the MAX () function, getting the correct, for me, date.