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  @NAME=NAME THEN  @row_number + 1  ELSE  1 END   AS  id
  ,VALUE
  , NAME
  ,@name
  ,@NAME:=name
  FROM `aaa` A,( SELECT  @NAME := 0,@row_number := 0)  AS  t
  ORDER BY NAME ,VALUE DESC 
  ) AS G
  WHERE G.ID=1