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


Read more here: https://stackoverflow.com/questions/68459959/how-to-get-the-maximum-value-of-table-in-mysql-carrying-the-respective-attribute

Content Attribution

This content was originally published by MapsEveryWhere at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: