The results of group by in mysql5.7 and group by in mysql5.5 are different.

  mysql, question

The same sql statements in 5.7 and 5.5 have different execution results. The main purpose is to get the record with the largest id in each group. The statement reads as follows:

select t2.* from (select t1.* from t_user t1 order by t1.id desc) as t2 group by t2.type;

Results of 5.7:

clipboard.png

Results of 5.5:

clipboard.png

The sql script is as follows:

DROP TABLE IF EXISTS `t_user`;
 CREATE TABLE `t_user` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) DEFAULT NULL,
 `phone` varchar(255) DEFAULT NULL,
 `gender` varchar(255) DEFAULT NULL,
 `type` varchar(255) DEFAULT NULL,
 `birth` datetime DEFAULT NULL,
 `is_delete` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;
 
 -- ----------------------------
 -- Records of t_user
 -- ----------------------------
 INSERT INTO `t_user` VALUES ('1', 'James', '0594-5397864', '0', '3', '2016-01-30 19:01:09', '1');
 INSERT INTO `t_user` VALUES ('2', 'Hayes', '0594-5392419', '1', '4', '2015-12-24 11:12:27', '1');
 INSERT INTO `t_user` VALUES ('3', 'Diana', '0594-5393520', '1', '5', '2016-03-21 13:03:50', '0');
 INSERT INTO `t_user` VALUES ('4', 'Rajah', '0594-5399812', '1', '4', '2015-11-26 02:11:35', '0');
 INSERT INTO `t_user` VALUES ('5', 'Daria', '0594-5397571', '0', '4', '2016-01-18 11:01:11', '1');
 INSERT INTO `t_user` VALUES ('6', 'Lee', '0594-5394539', '1', '1', '2015-10-23 08:10:23', '1');
 INSERT INTO `t_user` VALUES ('7', 'Cameran', '0594-5392867', '0', '4', '2016-11-16 12:11:08', '0');
 INSERT INTO `t_user` VALUES ('8', 'Wylie', '0594-5395349', '0', '5', '2017-07-06 04:07:27', '0');
 INSERT INTO `t_user` VALUES ('9', 'Bertha', '0594-5395287', '1', '1', '2017-02-08 12:02:45', '1');
 INSERT INTO `t_user` VALUES ('10', 'Fletcher', '0594-5399246', '0', '4', '2015-09-03 20:09:33', '0');
 INSERT INTO `t_user` VALUES ('11', 'Conan', '0594-5391546', '1', '5', '2017-05-15 09:05:23', '0');
 INSERT INTO `t_user` VALUES ('12', 'Raymond', '0594-5399666', '0', '3', '2015-10-20 05:10:05', '1');
 INSERT INTO `t_user` VALUES ('13', 'Noel', '0594-5397392', '1', '4', '2017-05-26 03:05:56', '0');
 INSERT INTO `t_user` VALUES ('14', 'Miriam', '0594-5399081', '0', '2', '2016-05-21 02:05:09', '0');
 INSERT INTO `t_user` VALUES ('15', 'Maya', '0594-5397242', '0', '3', '2016-10-24 02:10:50', '1');
 INSERT INTO `t_user` VALUES ('16', 'Winifred', '0594-5395142', '1', '1', '2017-03-15 02:03:43', '0');
 INSERT INTO `t_user` VALUES ('17', 'Elaine', '0594-5398478', '1', '3', '2017-03-08 15:03:03', '1');
 INSERT INTO `t_user` VALUES ('18', 'Robert', '0594-5397830', '0', '5', '2016-02-10 22:02:06', '0');
 INSERT INTO `t_user` VALUES ('19', 'Patrick', '0594-5396516', '0', '4', '2015-09-10 07:09:51', '0');
 INSERT INTO `t_user` VALUES ('20', 'Darrel', '0594-5397417', '0', '1', '2016-03-11 11:03:36', '0');
 INSERT INTO `t_user` VALUES ('21', 'Salvador', '0594-5399732', '1', '3', '2016-01-01 15:01:21', '0');
 INSERT INTO `t_user` VALUES ('22', 'Brandon', '0594-5396204', '1', '4', '2016-05-12 06:05:40', '1');
 INSERT INTO `t_user` VALUES ('23', 'Dorothy', '0594-5396783', '0', '1', '2016-12-12 10:12:59', '1');
 INSERT INTO `t_user` VALUES ('24', 'Kevyn', '0594-5398240', '0', '2', '2016-02-07 04:02:14', '1');
 INSERT INTO `t_user` VALUES ('25', 'Brody', '0594-5398774', '1', '1', '2016-12-11 20:12:36', '0');
select max(t2.id), t2.name, t2.phone, t2.gender, t2.type, t2.birth, t2.is_delete
 from t_user  t2
 group by t2.name, t2.phone, t2.gender, t2.type, t2.birth, t2.is_delete;

Syntax error, mysql is not so sensitive to group by, you will know by changing oracle