server sql 、MySQL 分组查询取最大值

---server sql

SELECT * FROM (
select fund_code,fundsname,invst_type,fund_status,
ROW_NUMBER() OVER(PARTITION BY FUND_CODE ORDER BY ESTAB_DATE DESC) AS RN
from pgenius.DBO.fnd_gen_info
where isvalid=1 and trade_mkt is null ) A
WHERE RN=1

--mysql
select * from (select * from pgenius.fnd_gen_info where isvalid=1 and trade_mkt is null ORDER BY ESTAB_DATE desc ) t group by FUND_CODE

这个有点坑，实际排序没有生效，取的是主键的最小的记录对应的整条记录

要优化

select t.* from (
select distinct(FUND_CODE) as tt,t1.* from pgenius.fnd_gen_info as t1
where isvalid=1 and trade_mkt is null order by estab_date desc
) as t group by FUND_CODE \G;

----and FUND_CODE='001064' 

以下是转来的详细分析过程，留着备用

mysql 分组后再获取组内排序的第一条数据

如图，如果我们按 number 分组后再按 is_bak 从大到小排序获取最大的第一条数据，那么最后结果就是 id 为 1，4，6 这 3 条。

select t.* from (
	select distinct(id) as tt,t1.* from a_t as t1 order by is_bak desc
) as t group by number order by id asc;

• 1
• 2
• 3