I have data in the below format:
| price | location | category |
|---|---|---|
| 10 | asia | a |
| 20 | africa | b |
| 30 | europe | c |
I want to find out for each location, which is the top category in terms of pricing. For example, for asia, a is the top category if sum(price) of a is highest in asia.
I can find the sum of prices for each location and category combination from the below query:
select sum(price), location, category
from table
group by location, category
This gives me sum of price of all locations with all category combinations.
How do I find that for all locations, which is the top category?