CODEWITHSUNDEEP
mysqlsql
yixi zhou
yixi zhou

Reputation: 297

How to select the first row which has the max value in each group?

I know how to select the max value of each group. But when there are multiple rows with the same value, I only want to select the first one, how can I do that?

For example, the table is something like:

Group  Name  Value
A A1 20
A A2 20
A A3 10
B B1 20
B B2 30
B B3 30
B B4 10

The result I want is 

A A1 20
B B2 30

Upvotes: 0

Views: 90

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

There is no such thing as "the first row", because SQL tables represent unordered sets. So, you need a column to specify what "first" is.

You can easily get one row. One method is a correlated subquery:

select t.*
from t
where (name, value) = (select name, value
                       from t t2
                       where t2.group = t.group
                       order by t2.value desc
                       limit 1
                      );

In MySQL 8+, the canonical way would use row_number():

select t.*
from (select t.*, row_number() over (partition by group order by value desc) as seqnum
      from t
     ) t
where seqnum = 1;

Upvotes: 1

Related Questions