Aggregate function (sum) question

[xcata]

Hi,
I have a table with the following columns:
product_id int,
category varchar(30),
ammount money,
cnt int

What I need is to have for each product the record that have ammount max for it. If exists many records with the the same ammount = max(ammount) for that product then we look forward to cnt and we take the record who has cnt = max(cnt) for the product and ammount = max. If we have many records who satisfied all that I said we must take only one (without importance).
For example:
product_id category ammount cnt
1 cat_1 100 1
1 cat_2 150 1
2 cat_1 100 1
2 cat_3 100 2
3 cat_1 100 1
3 cat_4 100 1

what I need is:
product_id category ammount cnt
1 cat_2 150 1
2 cat_3 100 2
3 cat_4 100 1 or (this or the next)
3 cat_1 100 1
(3 rows)
--for product 1 we choose that record because we have ammount max only once
--for product 2 we choose that record because we have 2 records with ammount max then we consider the other criter max(cnt)
-- for 3 we must only one of the 2 records

Of course it exist a solution with a temporary table who holds each product + max ammount. After that we make another select with 2 subquerys but it does not sound optimal for me.

Thanks and sorry for my poor english.

 

[tlbroadbent]

Try this query. You'll need to chnage names to match your table and column names.

SELECT
t.Product_Id,
Max(t.Category) As Category,
t.Ammount,
t.Cnt

FROM tblname t

INNER JOIN
(SELECT
product_id,
Max(Ammount) AS MaxAmt,
Max(Cnt) AS MaxCnt
FROM tblname
GROUP BY product_id) AS m

INNER JOIN t.product_id = m.product_id

WHERE t.Ammount=.MaxAmt
AND t.Cnt=m.MaxCnt

GROUP BY
t.product_id,
t.Ammount,
t.cnt Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[xcata]

Hi,

Thanx, I'll try it.