Using two aggregate function - min and max on same query

Question

Here is my product table's data -

product_id  category    discount
454           C-10       10 
357           C-10       9
64            C-10       10
294           C-11       17 
449           C-11       17
471           C-11       17 
89            C-11       12 
56            C-11       10

I want to get the max discount for every product category and if any category has multiple products having same discount, the product having the minimum product_id should be selected.

Desired output -

product_id  category    discount
64          C-10        10
294         C-11        17

I tried below two query but not working -

select category,min(product_id),max(discount)
from Product 
group by category

Your help is very much appreciated. Thanks!

Answer 1

Using ROW_NUMBER is helpful here:

WITH cte AS (
    SELECT product_id, category, discount,
        ROW_NUMBER() OVER (PARTITION BY category
            ORDER BY discount DESC, product_id) rn
    FROM Product
)

SELECT product_id, category, discount
FROM cte
WHERE rn = 1;

Or, we could even do this without using a subquery/CTE:

SELECT TOP 1 WITH TIES product_id, category, discount
FROM Product
ORDER BY
    ROW_NUMBER() OVER (PARTITION BY category
        ORDER BY discount DESC, product_id);