"Eric Pankoke" <[email protected]> wrote
in message news:6a90e70a6f404948991a0380e6793...@gamesmachine
The following query:
>
> SELECT MfgProductID, ProductID, SUM(Weight) As MaxWeight FROM
> dominantmaterials GROUP BY MfgProductID, ProductID
>
> Gets me most of the way to what I need. What I'm really after,
> however, is the ProductID for each MfgProductID that has the greatest
> combined weight from all MfgProductID / ProductID rows.
select m, p,
(select sum(Weight) from dominantmaterials
where MfgProductId=m and ProductID=p) w
from
(select m,
(select ProductID from dominantmaterials
where MfgProductId = m
group by ProductID
order by sum(Weight) desc limit 1) p
from
(select distinct MfgProductId m from dominantmaterials));
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
