n6n7PB2C

1Return records after group by with a specific MAX(field)
2SELECT forum_categories.title, COUNT(DISTINCT forum_topics.id) AS total_topics, 
3SUM(CASE WHEN forum_messages.original=0 THEN 1 ELSE 0 END) AS total_replies, forum_messages.author, 
4MAX(forum_messages.date) AS last_message, SUM(CASE WHEN r.user IS NULL THEN 1 ELSE 0 END) to_view
5FROM forum_categories
6JOIN forum_topics ON forum_topics.category_id=forum_categories.id 
7LEFT OUTER JOIN (SELECT topic, user FROM forum_visits WHERE user='userA') r ON forum_topics.id=r.topic
8JOIN forum_messages ON forum_messages.topic_id=forum_topics.id
9GROUP BY forum_categories.id 
10ORDER BY forum_categories.date
11	
12CREATE TABLE IF NOT EXISTS `forum_categories` (
13  `id` int(11) unsigned NOT NULL auto_increment,
14  `title` varchar(255) NOT NULL,
15  `description` varchar(255) NOT NULL,
16  `date` datetime NOT NULL,
17  PRIMARY KEY  (`id`)
18) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
19
20CREATE TABLE IF NOT EXISTS `forum_topics` (
21  `id` int(11) unsigned NOT NULL auto_increment,
22  `category_id` int(11) unsigned NOT NULL,
23  `title` varchar(255) NOT NULL,
24  `author` varchar(255) NOT NULL,
25  `date` datetime NOT NULL,
26  `view` int(11) unsigned NOT NULL default '0',
27  `sticky` tinyint(11) unsigned NOT NULL default '0',
28  PRIMARY KEY  (`id`)
29) ENGINE=MyISAM AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;    
30
31CREATE TABLE IF NOT EXISTS `forum_messages` (
32  `id` int(11) unsigned NOT NULL auto_increment,
33  `topic_id` int(11) unsigned NOT NULL,
34  `author` varchar(255) NOT NULL,
35  `message` mediumtext NOT NULL,
36  `date` datetime NOT NULL,
37  `original` tinyint(11) unsigned NOT NULL default '0',
38  PRIMARY KEY  (`id`)
39) ENGINE=MyISAM AUTO_INCREMENT=29 DEFAULT CHARSET=utf8;
40
41CREATE TABLE IF NOT EXISTS `forum_visits` (
42  `id` int(11) unsigned NOT NULL auto_increment,
43  `topic` int(11) unsigned NOT NULL,
44  `user` varchar(255) NOT NULL,
45  PRIMARY KEY  (`id`),
46  UNIQUE KEY `forum_visits_unique_idx` (`topic`,`user`)
47) ENGINE=MyISAM AUTO_INCREMENT=131 DEFAULT CHARSET=utf8;
48	
49SELECT forum_categories.title,
50COUNT(DISTINCT forum_topics.id) AS total_topics,
51SUM(CASE WHEN forum_messages.original=0 THEN 1 ELSE 0 END) AS total_replies,
52t2.author, t2.last_message
53
54-- first get the counters per category
55FROM forum_categories
56JOIN forum_topics ON forum_topics.category_id=forum_categories.id
57JOIN forum_messages ON forum_messages.topic_id=forum_topics.id
58
59-- Then join a query to get last message per category
60JOIN (SELECT forum_categories.id, forum_messages.author,
61     forum_messages.date AS last_message
62     FROM forum_categories
63     JOIN forum_topics ON forum_topics.category_id=forum_categories.id
64     JOIN forum_messages ON forum_messages.topic_id=forum_topics.id
65     JOIN (SELECT MAX(m.date) as date, top.category_id
66          FROM forum_messages m
67          JOIN forum_topics top ON m.topic_id = top.id
68          GROUP BY top.category_id) as t
69          ON t.category_id = forum_topics.category_id AND t.date = forum_messages.date
70          GROUP BY forum_categories.id) t2
71     ON t2.id = forum_categories.id
72
73GROUP BY forum_categories.id
74	
75SELECT
76  forum_stats.*,   /* just repeat the already pulled columns (expand it if needed) */
77  forum_messages.* /* and here you may actually want to be more specific as to
78                       what else you would like to pull from forum_messages */
79FROM (
80  SELECT
81    forum_categories.id AS category_id,
82    forum_categories.title,
83    COUNT(DISTINCT forum_topics.id) AS total_topics, 
84    SUM(CASE WHEN forum_messages.original=0 THEN 1 ELSE 0 END) AS total_replies,
85    MAX(forum_messages.date) AS last_message,
86    SUM(CASE WHEN r.user IS NULL THEN 1 ELSE 0 END) AS to_view,
87    forum_categories.date
88  FROM forum_categories
89    JOIN forum_topics ON forum_topics.category_id=forum_categories.id 
90    LEFT OUTER JOIN (
91      SELECT topic, user FROM forum_visits WHERE user='userA'
92    ) r ON forum_topics.id=r.topic
93    JOIN forum_messages ON forum_messages.topic_id=forum_topics.id
94  GROUP BY forum_categories.id
95) forum_stats
96  JOIN forum_topics ON forum_topics.category_id=forum_stats.category_id
97  JOIN forum_messages ON forum_messages.topic_id=forum_topics.id
98    AND forum_messages.date=forum_stats.last_message
99ORDER BY forum_stats.date