Search code examples
mysqlsqldatabaseselectgreatest-n-per-group

MySQL Select max() from every Value

I there a way to get the max() Value of every Value? I have a table like this:

id primary key
name foreign key
age

and I need the highes age of every Name. For example:

ID  NAME   AGE
1,  Marco, 12
2,  Jason, 23
3,  Tom,   5
4,  Marco, 16
5,  Jason, 22

The output should be:

ID  NAME   AGE
2,  Jason, 23
3,  Tom,   5
4,  Marco, 16

Is this possible and how? Thank you.

Solution

  • You can get the max value of each column using aggregation:

    select max(id), name, max(age)
from t
group by name;

    But if you want the complete row with the max age, then that would be:

    select t.*
from t
where t.age = (select max(t2.age) from t t2 where t2.name = t.name);