CODEWITHSUNDEEP
mysqlsqlmysql-5.7
MapsEveryWhere
MapsEveryWhere

Reputation: 57

How to get the maximum value of table in MySQL carrying the respective attributes

I have the following example_table resulting from a nested query:

id    site_ref     area  
-------------------------------
91    Lake SW       0.23
91    Lake MP       3.89
93    Lake SW       0.56
93    Lake MP       0.05

I want to get the maximum area per id , carrying the respective site_ref with it. I used the following SQL:

select id,  site_ref, max(area) from example_table  GROUP BY id

What I get is (wrong site_ref):

id    site_ref     area  
-------------------------------
91    Lake SW       0.56
93    Lake SW       3.89

What I want is:

id    site_ref     area  
-------------------------------
91    Lake MP       3.89
93    Lake SW       0.56

Upvotes: 0

Views: 23

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270523

One method is a correlated subquery:

select et.*
from example_table et
where et.area = (select max(et2.area) from example_table et2 where et2.id = et.id);

Upvotes: 2

Related Questions