inControl inControl - 4 months ago 7
MySQL Question

How to group by on a highest value

So, for example i've got the following table;

ID COUNTRY VALUE
--------------------- -------------------- --------------------
1 India 12000
2 India 11000
3 UK 11000
4 India 15000
5 Canada 11000


And I would like to group by Country but only have the country with the highest value show up, if I would just use a group by query like:

SELECT * FROM countries GROUP BY country


I would get;

ID COUNTRY VALUE
--------------------- -------------------- --------------------
1 India 12000
3 UK 11000
5 Canada 11000


Where the value for india would be 12000. I would like the query to group on the highest value for the group by on country like:

ID COUNTRY VALUE
--------------------- -------------------- --------------------
3 UK 11000
4 India 15000
5 Canada 11000


So it's grouped on the highest value which is 15000.

Answer

You can use the MAX aggregate function.

select
  country,
  max(value) value
from countries
group by
  country

See the live example.

Edit: The original solution was only correct due to the nature of the data. I've removed the ID from the first query, to correct the mistake. Here is another solution (based on @Juan Carlos Oropeza's work - thank you) that will return the ID and eliminate the ties.

select
  min(x.id) id,
  x.country,
  x.value
from (
  select
    c.*
  from countries c
    left join countries c1 on c.value < c1.value and c.country = c1.country
  where c1.country is null    
) x
group by
  x.country,
  x.value
;

See the live example - I've modified the data to cover edge cases mentioned above.

Comments