I have a set of data which shows products sold in a period and the category they belong to. So multiple products in each category and one of hose products sold the most in each category and I want to show that data. For example:
Product Category Qty
1 1 10
2 1 9
3 1 4
4 2 20
5 2 10
6 2 5
7 3 10
8 3 2
I want to see the product with the highest quantity in each category:
Product Category Qty
1 1 10
4 2 20
7 3 10
Do we use partition over or a sub-query