Sometimes we have this requirement: querying grouped results as subtables, while the main table requires the maximum or minimum value of a column in each group of subtables. But if adding ORDER BY after GROUP BY just sorts the grouped data, it does not affect the grouped data. There are two ways to address this requirement:
1. Through ROLLUP.
2. Query by self-connection.
First, let's create some new test data:
------------------------------------------------------------------------------------ DROP TABLE IF EXISTS `test`; CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `age` int(3) DEFAULT NULL, `grade` int(1) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8; INSERT INTO `test` VALUES ('1', 'Zhang San', '18', '1'); INSERT INTO `test` VALUES ('2', 'Li Si', '19', '1'); INSERT INTO `test` VALUES ('3', 'King Five', '20', '1'); INSERT INTO `test` VALUES ('4', 'Zhao Six', '18', '1'); INSERT INTO `test` VALUES ('5', 'Master Hongqi', '20', '2'); INSERT INTO `test` VALUES ('6', 'kze-jqg', '19', '2'); INSERT INTO `test` VALUES ('7', 'Look crazy', '18', '2'); INSERT INTO `test` VALUES ('8', 'Iron Man', '21', '3'); INSERT INTO `test` VALUES ('9', 'Spider-Man', '20', '3'); INSERT INTO `test` VALUES ('10', 'The Incredible Hulk', '18', '3'); ------------------------------------------------------------------------------------
-- GROUP BY and ORDER BY Ways SELECT *,count(*) FROM test GROUP BY grade ORDER by age DESC
We can see that this way we don't get the data we want and that the oldest people aren't shown in each grade. 
-- ROLLUP Ways SELECT *, max(age), count(*) FROM test GROUP BY grade WITH ROLLUP
We can see that this way we get the maximum age for each grade, but it's not entirely possible that the records shown will not meet our needs. 
-- Ways of self-connecting queries SELECT *, (SELECT COUNT(DISTINCT test.age) FROM test WHERE grade = t.grade) count FROM test t WHERE NOT EXISTS (SELECT 1 FROM test WHERE grade = t.grade AND age > t.age)
We can see that this is a way to show the record for the maximum age, to meet demand and to change our conditions according to different conditions. 