0

I have the following table called values in a MySQL 5.7 database:

value1 value2 value3
foo 7 something4
foo 5 something1
foo 12 anything3
bar 3 something7
bar 18 anything5
bar 0 anything8
baz 99 anything9
baz 100 something0

I want to GROUP BY value1 and always keep the row with the highest value in value2.

So the expected result of the table above is:

value1 value2 value3
foo 12 anything3
bar 18 anything5
baz 100 something0

How can I do that with SQL?

Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26
David
  • 2,898
  • 3
  • 21
  • 57

2 Answers2

1

You don't want to group. You want to filter! You are keeping the original rows.

One method uses a correlated subquery:

select v.*
from values v
where v.value2 = (select max(v2.value2)
                  from values v2
                  where v2.value1 = v.value1
                 );
Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Is `from t t2 where` a typo? – David Apr 28 '21 at 15:40
  • 1
    @David . . . But I see you included a table name in the question, so I used it in the answer. Note that `values` is a poor choice for a table name because it is a SQL keyword (think `insert`). – Gordon Linoff Apr 28 '21 at 15:53
1

Schema and insert statements:

 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');

Query#1 (using inner join)

 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

Output:

value1 value2 value3
foo 12 anything3
bar 18 anything5
baz 100 something0

Query#2 (using subquery in where clause)

 select m.*
 from mytable m
 where m.value2 = (select max(m2.value2) from mytable  m2 where m2.value1 = m.value1);

Output:

value1 value2 value3
foo 12 anything3
bar 18 anything5
baz 100 something0

db<fiddle here

Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26