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!

Tim Biegeleisen · Accepted Answer · 2019-03-05 09:08:40Z

4

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);

edited Mar 5, 2019 at 9:08

answered Mar 5, 2019 at 9:03

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fahmi · Accepted Answer · 2019-03-05 09:05:10Z

1

use row_number()

select * from
(
select *,row_number() over(partition by category order by discount desc, poroduct_id asc) rn
from tablename
)A where rn=1

OR use correlated subquery

select * from tablename a where discount in 
  (select max(discount) from tablename b where a.category=b.category 
     having min(b.product_id)=a.product_id)

answered Mar 5, 2019 at 9:05

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Comments

Zaynul Abadin Tuhin · Accepted Answer · 2019-03-05 09:25:29Z

1

use outer apply

with cte as    
(
select 454 as product_id, 'C-10'  as category, 10 as discount union all
select 357,'C-10',9 union all
select 64,'C-10',10 union all
select 294,'C-11',17 union all
select 449,'C-11',17 union all
select 471,'C-11',17 union all
select 89,'C-11', 12 union all
select 56,'C-11', 10 

) select distinct p1.category,a.product_id,a.discount
 from cte p1
 outer apply ( select top 1 p2.*
               from cte p2 where p1.category=p2.category  
                order by discount desc, product_id asc

             ) a

output

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

demo link

edited Mar 5, 2019 at 9:25

answered Mar 5, 2019 at 9:10

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Collectives™ on Stack Overflow

Using two aggregate function - min and max on same query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related