mysql group by max 取出某一字段分组中最大的值

SELECT
	* 
FROM
	(
	SELECT
		record.device_warn_record_id,
		fac.factor_en_name,
		fac.factor_name,
		maxd.factor_id,
		maxd.factor_rtd,
		maxd.rtd_flag,
		maxd.rtd_datatime,
		record.device_code,
		maxd.rtd_datatime AS warnMaxTime,
		unit.unit_name,
		unit.unit_code 
	FROM
		bx_well_device_warn_record record
		LEFT JOIN bx_well_factor fac ON fac.factor_id = record.factor_id 
		AND fac.del_flag = '0'
		LEFT JOIN bx_well_unit unit ON unit.unit_id = fac.factor_unit_id 
		AND unit.del_flag = '0'
		LEFT JOIN (
		SELECT
			* 
		FROM
			(
			SELECT
				det.device_warn_record_id,
				det.factor_rtd,
				det.rtd_flag,
				det.rtd_datatime,
				det.factor_id 
			FROM
				bx_well_device_warn_detail det 
			ORDER BY
				rtd_datatime DESC 
				LIMIT 1,
				100000 
			) dett 
		GROUP BY
			dett.device_warn_record_id 
		) maxd ON maxd.device_warn_record_id = record.device_warn_record_id 
	WHERE
		record.factor_id IN ( '1294102359420841986' ) 
		AND record.`status` = 1 
	ORDER BY
		record.status_up_time DESC 
		LIMIT 1,
		100000 
	) ddet 
GROUP BY
	ddet.factor_id

查看一下这条sql在两个不同版本数据库的执行计划：
MySql 5.7.20:

MySql 5.5.57:

  
对比可以发现5.7版本的MySql在执行这条sql时缺少了一个derived操作，通过查阅相关资料了解到MySql 5.7对子查询进行了优化，认为子查询中的order by可以进行忽略，只要Derived table里不包含如下条件就可以进行优化：

UNION clause
GROUP BY
DISTINCT
Aggregation
LIMIT or OFFSET
这里把链接放上：5.7中Derived table变形记

最后放上相应的解决办法：

--方法一，仅适用于低于5.7版本的MySql--
select * from (select * from A order by time desc) a GROUP BY a.name; 

--方法二--
select * from (select * from A order by time desc limit 999999) a GROUP BY a.name;

--方法三--
select * from A a where N > (select count(*) from A b where b.id= a.id and a.time< b.time) order by a.name,a.time desc;

方法二中使用limit，需要limit的范围足够大能包括所有数据，并且每种分类只会显示一条数据，但是数据较多时运行效率要比方法三快上很多，方法三能够控制每种分类显示多少条数据，把N换成需要显示对应的数字即可。