Return records after group by with a specific MAX(field)

I search on SO many topics like this, but I can't apply to my query. This is :

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, SUM(CASE WHEN r.user IS NULL THEN 1 ELSE 0 END) to_view
FROM forum_categories
JOIN forum_topics ON forum_topics.category_id=forum_categories.id 
LEFT OUTER JOIN (SELECT topic, user FROM forum_visits WHERE user='userA') r ON forum_topics.id=r.topic
JOIN forum_messages ON forum_messages.topic_id=forum_topics.id
GROUP BY forum_categories.id 
ORDER BY forum_categories.date

It works: the only problem is that return only the field forum_messages.date with MAX data; intead, I'd like to return the whole row with that MAX field (so the corrispondent author, for example).

So what I should return, in less words, is :

1. the title for each category; *(at the moment this works)
2. the number of the topics for that category; (at the moment this works)
3. the number of replies for all topics for that category; (here there is another condition as you can see, the counter of that replies is get by the message with the filed original=0) (at the moment this works)
4. the author/data for the last message for that category (HERE there is the problem : it return correctly only the date, not the author);
5. a flag that indicate if there is any topic that userA haven't been checked yet; (also at the moment works: if SUM return somethings more high than 0, there is a topic not viewed)
6. this query is supposted to be as faster as possible, because the tables could be very big;

For details, these are my actual tables :

CREATE TABLE IF NOT EXISTS `forum_categories` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `title` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `forum_topics` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `category_id` int(11) unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `author` varchar(255) NOT NULL,
  `date` datetime NOT NULL,
  `view` int(11) unsigned NOT NULL default '0',
  `sticky` tinyint(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;    

CREATE TABLE IF NOT EXISTS `forum_messages` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `topic_id` int(11) unsigned NOT NULL,
  `author` varchar(255) NOT NULL,
  `message` mediumtext NOT NULL,
  `date` datetime NOT NULL,
  `original` tinyint(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=29 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `forum_visits` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `topic` int(11) unsigned NOT NULL,
  `user` varchar(255) NOT NULL,开发者_开发百科
  PRIMARY KEY  (`id`),
  UNIQUE KEY `forum_visits_unique_idx` (`topic`,`user`)
) ENGINE=MyISAM AUTO_INCREMENT=131 DEFAULT CHARSET=utf8;

Hope that someone can help me!

We can use the query from your previous question to get the message author and the message date, and the query above to get the counters (topics and replies), and join them together:

EDIT: This query works (tested). BUT it got a little complex and has 2 subqueries in it, so I hope someone else posts a better simpler one. If the DB gets very big, this one may get sluggish.

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,
t2.author, t2.last_message

-- first get the counters per category
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

-- Then join a query to get last message per category
JOIN (SELECT forum_categories.id, 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
          GROUP BY forum_categories.id) t2
     ON t2.id = forum_categories.id

GROUP BY forum_categories.id

To supplement the current output with the missing data, I would probably go like this:

SELECT
  forum_stats.*,   /* just repeat the already pulled columns (expand it if needed) */
  forum_messages.* /* and here you may actually want to be more specific as to
                       what else you would like to pull from forum_messages */
FROM (
  SELECT
    forum_categories.id AS category_id,
    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,
    MAX(forum_messages.date) AS last_message,
    SUM(CASE WHEN r.user IS NULL THEN 1 ELSE 0 END) AS to_view,
    forum_categories.date
  FROM forum_categories
    JOIN forum_topics ON forum_topics.category_id=forum_categories.id 
    LEFT OUTER JOIN (
      SELECT topic, user FROM forum_visits WHERE user='userA'
    ) r ON forum_topics.id=r.topic
    JOIN forum_messages ON forum_messages.topic_id=forum_topics.id
  GROUP BY forum_categories.id
) forum_stats
  JOIN forum_topics ON forum_topics.category_id=forum_stats.category_id
  JOIN forum_messages ON forum_messages.topic_id=forum_topics.id
    AND forum_messages.date=forum_stats.last_message
ORDER BY forum_stats.date

Of course, this assumes that forum_messages.date is not just a date, but a timestamp and that no two messages can share absolutely the same timestamp.