ORDER with GROUP BY in attached table?

Question

MySQL

There is a query that selects bloggers, their info and latest post

SELECT max(p.post_date), p.post_date,u.ID, u.display_name AS name, ud.meta_value AS bio, yim.meta_value AS yim, u.user_email, u.user_url, p.post_title, p.post_name
FROM wp_users AS u
LEFT JOIN wp_usermeta AS ud ON ( u.ID = ud.user_id AND ud.meta_key =  'description' ) 
LEFT JOIN wp_usermeta AS yim ON ( u.ID = yim.user_id AND yim.meta_key =  'yim' ) 
LEFT JOIN wp_usermeta AS role ON ( u.ID = role.user_id AND role.meta_key =  'wp_capabilities' ) 
LEFT JOIN wp_posts AS p ON ( u.ID = p.post_author ) 
WHERE role.meta_value LIKE  '%blogger%' AND p.post_status =  'publish'
GROUP BY u.ID 
ORDER BY p.post_date DESC

But the articles must be the last, and in this situation they are arbitrary;
The question is, how to choose bloggers with their latest entry, and not the penultimate one or some other?

Answer 1

I am not a MySQL expert, but in general, according to the SQL standard, only those fields that are either specified in the group by block (that is, by which grouping is carried out) or are passed to a group function such as sum, max, etc., can be specified in the select block. d.
Judging by the documentation , MySQL allows you not to adhere to this rule, but then for fields that do not satisfy the conditions voiced above, an arbitrary row from the group is selected.
In order to disable the extended interpretation of GROUP BY in MySql, you need to enable the ONLY_FULL_GROUP_BY mode
Regarding your query, reselecting the p.post_date column after you've indicated that you want to get records with the latest date - max(p.post_date) obviously doesn't make sense and breaks the grouping. Remove it, and in general it is better to stick to the standard, so your queries will be easier to port if you migrate to another DBMS, and there will be less misunderstandings.

Answer 2

Select ... over (partition by ... ORDER BY ...)
from ..