mysql gets the detailed explanation of the maximum value instance of each group after grouping

mysql gets the detailed explanation of the maximum value instance of each group after grouping

1. The test database table is as follows:

create table test 
( 
  `id` int not null auto_increment, 
  `name` varchar(20) not null default '', 
  `score` int not null default 0, 
  primary key(`id`) 
)engine=InnoDB CHARSET=UTF8; 

2. Insert the following data:

mysql> select * from test; 
+----+----------+-------+ 
| id | name   | score | 
+----+----------+-------+ 
| 1 | jason  |   1 | 
| 2 | jason  |   2 | 
| 3 | jason  |   3 | 
| 4 | linjie  |   1 | 
| 5 | linjie  |   2 | 
| 6 | linjie  |   3 | 
| 7 | xiaodeng |   1 | 
| 8 | xiaodeng |   2 | 
| 9 | xiaodeng |   3 | 
| 10 | hust   |   2 | 
| 11 | hust   |   3 | 
| 12 | hust   |   1 | 
| 13 | haha   |   1 | 
| 14 | haha   |   2 | 
| 15 | dengzi  |   3 | 
| 16 | dengzi  |   4 | 
| 17 | dengzi  |   5 | 
| 18 | shazi  |   3 | 
| 19 | shazi  |   4 | 
| 20 | shazi  |   2 | 
+----+----------+-------+ 

3. The following is the key point, the purpose is to group according to name, and then after grouping, get the most score scores in each group, and sql is as follows

select a.* from test a inner join (select name,max(score) score from test group by name)b on a.
name=b.name and a.score=b.score order by a.name; 

Of course, the last order by a. name above can be removed

4. The test results are as follows:

+----+----------+-------+ 
| id | name   | score | 
+----+----------+-------+ 
| 3 | jason  |   3 | 
| 6 | linjie  |   3 | 
| 9 | xiaodeng |   3 | 
| 11 | hust   |   3 | 
| 14 | haha   |   2 | 
| 17 | dengzi  |   5 | 
| 19 | shazi  |   4 | 
+----+----------+-------+ 

5. Many methods on the Internet are wrong, such as the following 1. Pro-test is not possible

select * from (select * from test order by score desc) t group by name order by score desc limit 4; 
select score,max(score) from test group by name; 
select * from test where score in (select max(score) from test group by name); 
select * from test where score in (select substring_index(group_concat(score order by score desc separator ','),',',1) from test group by name); 
 
select * from (select name,score,ROW_NUMBER() over(group by name order by score desc) as rowNum from test) rank where rank.rowNum <=1 order by rank.score desc; 
 
select * from( select StoresNo,[CustomerCaseNo],[PaymentsTime], ROW_NUMBER() over(partition by CustomerCaseNo order by [PaymentsTime] desc) as rowNum 
from BAL_paymentsSwiftInfo where StoresNo='zq00000034') ranked where ranked.rowNum <= 1 order by ranked.CustomerCaseNo, ranked.PaymentsTime desc 
 
select * from (select * from test order by score desc) as a group by a.name; 

Thank you for reading, hope to help everyone, thank you for your support to this site!