Selecting rows with max value, in correlation to two other columns

Question

I have a table with three columns: region, country, count. I want to reduce my table to rows of region, country and count - where count is maximal among the region.

For example, if I have the following table:

region | country | count
asia   | jo      | 12
asia   | ir      | 12
asia   | il      | 10  
europe | fr      |  8
europe | it      |  2

I'd expect to get in return:

region | country | count
asia   | jo      | 12
asia   | ir      | 12
europe | fr      |  8

Answer 1

You can achieve this in this way: First you need to group the data by the region field and get the maximum value. Then execute a simple IN condition with fields region and count

select * from my_table WHERE (region, count) in (select region, MAX(count) from my_table  GROUP BY region)

Demo in sqldaddy.io