MySQL query already GROUPed and ORDERed : how to ORDER inside the GROUPs?

I have this quer开发者_StackOverflowy:

SELECT id_user, COUNT(*) as count
FROM posts
GROUP BY id_user
ORDER BY COUNT(*) DESC

which gives me the id_user ordered by occurrences, and the number of each occurrence. Can I get, in the same request, the LAST post from each 'id_user'? i.e. I want to select the last 'post' too, but when I do

SELECT id_user, post, COUNT(*) as count

Tthe value in 'post' isn't the last one (nor the first one; actually I don't know how groups are ordered). Should I run another query?

I believe u can accomplish this by adding max(post_id) last_post to your select.

This ought to do it in one query:

SELECT 
  p.id_user,
  ap.post AS last_post,
  COUNT(*) as count
FROM 
  posts p
  JOIN posts ap on (
    p.id_user = ap.id_user
    AND ap.post_id = (
      SELECT MAX(post_id) FROM posts ip WHERE p.id_user = ip.id_user
    )
GROUP BY 
  p.id_user,
  ap.post
ORDER BY 
  COUNT(*) DESC