GROUP BY value1，然后选择组中值2最高的行

Question

我在MySQL 5.7数据库中有一个名为values的表：

something1//tr>任何东西3something7[/tr>something0//tr>

value1	value2	value3
foo	7	某物4
foo	5
foo	12
bar	3
bar	18	任何东西5
bar	0	任何东西8
baz	99	任何东西9
baz	100

架构和插入语句：

create table mytable (value1 varchar(50),  value2   int, value3 varchar(50));
insert into mytable values('foo',  7,  'something4');
insert into mytable values('foo',  5,  'something1');
insert into mytable values('foo',  12, 'anything3');
insert into mytable values('bar',  3,  'something7');
insert into mytable values('bar',  18, 'anything5');
insert into mytable values('bar',  12, 'anything8');
insert into mytable values('baz',  99, 'anything9');
insert into mytable values('baz',  100,    'something0');

查询#1(使用内部联接(

select m.* from mytable m inner join 
(select value1, max(value2) maxvalue2 from mytable group by value1) m2
on m.value1=m2.value1 and m.value2=m2.maxvalue2

输出：

nything5[/tr>omething0//tr>

value1	value2	value 3
foo	12	任何东西3
bar	18
baz	100

您不希望分组。你想过滤！您保留了原始行。

一种方法使用相关的子查询：

select v.*
from values v
where v.value2 = (select max(v2.value2)
from values v2
where v2.value1 = v.value1
);