I realize this is more of a general SQL question, but I figured while I
was digging for the answer one of you would probably already know it.  I
have a table where the three import fields for my situation are
MfgProductID, ProductID and Weight.  For any given MfgProductID there
can be multiple rows, and for any given MfgProductID / ProductID
combination there can be multiple rows.  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.  I hope this makes sense,
but as an illustration:

 

Mfg  |  Product ID  |  Weight

1     |   1               |  10

1     |   1               |  10

1     |   2               |  15

 

When I'm done I want only 1 row for MfgProductID 1, where ProductID is 1
(since the combined total of rows with ProductID 1 is 20 for weight,
which is greater than the single row of ProductID 2 at weight 15).
Sorry for a rambling explanation for what I'm sure is a simple solution.

 

Eric Pankoke

Mobile Games Reviewer

http://www.rustysabre.com/

http://www.technobrains.com/

 

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to