Problema al agrupar

Tengo esta consulta MySql:

SELECT forum_categories.title, forum_messages.author, forum_messages.date AS last_message FROM forum_categories JOIN forum_topics ON forum_topics.category_id=forum_categories.id JOIN forum_messages ON forum_messages.topic_id=forum_topics.id WHERE forum_categories.id=6 ORDER BY forum_categories.date ASC 

Y el resultado es el siguiente:

 Welcome daniel 2010-07-09 22:14:49 Welcome daniel 2010-06-29 22:14:49 Welcome luke 2010-08-10 20:12:20 Welcome skywalker 2010-08-19 22:12:20 Welcome delicious 2010-10-09 19:12:20 Welcome daniel 2011-11-05 23:12:20 Welcome pierre 2011-11-05 23:12:22 

Ahora, me gustaría agruparlo usando la date MAX. Entonces la consulta se convierte en:

 SELECT forum_categories.title, forum_messages.author, forum_messages.date AS last_message, MAX(forum_messages.date) FROM forum_categories JOIN forum_topics ON forum_topics.category_id=forum_categories.id JOIN forum_messages ON forum_messages.topic_id=forum_topics.id WHERE forum_categories.id=6 GROUP BY forum_categories.id ORDER BY forum_categories.date ASC 

¡PERFECTO! La date tomada es correcta: 2011-11-05 23:12:22 (la date MÁXIMA para esa agrupación). Pero también me refiero a este comportamiento para get el usuario adecuado para esa línea (en el ejemplo Pierre ).

Pero toma otro.

¿Por qué? No selecciona toda la fila? ¿Y cómo puedo solucionar este problema? Aclamaciones

EDITAR

De hecho, debería aplicar esta agrupación a esta consulta completa:

 SELECT forum_categories.title, COUNT(DISTINCT forum_topics.id) AS total_topics, SUM(CASE WHEN forum_messages.original=0 THEN 1 ELSE 0 END) AS total_replies, forum_messages.author, MAX(forum_messages.date) AS last_message FROM forum_categories JOIN forum_topics ON forum_topics.category_id=forum_categories.id JOIN forum_messages ON forum_messages.topic_id=forum_topics.id GROUP BY forum_categories.id ORDER BY forum_categories.date 

Prueba este (avísame si funciona):

 SELECT forum_categories.title, forum_messages.author, forum_messages.date AS last_message FROM forum_categories JOIN forum_topics ON forum_topics.category_id=forum_categories.id JOIN forum_messages ON forum_messages.topic_id=forum_topics.id JOIN (SELECT MAX(m.date) as date, top.category_id FROM forum_messages m JOIN forum_topics top ON m.topic_id = top.id GROUP BY top.category_id) as t ON t.category_id = forum_topics.category_id AND t.date = forum_messages.date WHERE forum_categories.id=6 GROUP BY forum_categories.id 

Aquí hay una segunda opción:

 SELECT forum_categories.title, forum_messages.author, forum_messages.date AS last_message FROM forum_categories JOIN forum_topics ON forum_topics.category_id=forum_categories.id JOIN forum_messages ON forum_messages.topic_id=forum_topics.id WHERE forum_categories.id=6 AND forum_messages.date = (SELECT MAX(date) FROM forum_messages t WHERE t.topic_id = forum_topics.id) GROUP BY forum_categories.id ORDER BY forum_categories.date ASC 

Debe include forum_categories.title y forum_messages.author en su cláusula GROUP BY, ya que no son funciones agregadas. Piense en GROUP BY como diciendo qué valores en el set de resultados deberían "juntarse" en una fila.

También lo más probable es que desee orderar por una columna en su set de resultados, como last_message (en lugar de forum_messages.date)

Asi que:

 SELECT forum_categories.title, forum_messages.author, forum_messages.date AS last_message, MAX(forum_messages.date) FROM forum_categories JOIN forum_topics ON forum_topics.category_id=forum_categories.id JOIN forum_messages ON forum_messages.topic_id=forum_topics.id WHERE forum_categories.id=6 GROUP BY forum_categories.title, forum_messages.author ORDER BY last_message ASC