MYSQL实现排序分组取第一条sql

• 数据准备,创建aaa表，实现ABC取最大值 A 5 ,B 6,C 7

• 

• 实现sql 方式1  使用limit关键字使排序生效

• 

SELECT * FROM (
 SELECT * FROM `aaa` A 
 ORDER BY name, VALUE DESC 
 LIMIT 1000000
) AS G GROUP BY NAME 
;

• 实现sql 方式2  使用distinct关键字使排序生效

• 

• SELECT * FROM(
   SELECT DISTINCT * FROM `aaa` A
  ORDER BY NAME ,VALUE DESC 
  ) AS G GROUP BY NAME 
  ;

• 实现sql 方式3  使用变量方式(推荐)

• 

• select * from (
  	 select
  	 @row_number := case when  @v_name= name then  @row_number + 1  else  1 end  as  id
  	,name
  	,value
  	,@v_name:=name
  	from aaa A,( select  @v_name := '',@row_number)  as  t
  	order by name ,value desc 
  ) as g
  where g.id=1