Max Value In Group By Scenario

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Max Value In Group By Scenario

Eric Pankoke
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Max Value In Group By Scenario

Igor Tandetnik
"Eric Pankoke" <[hidden email]> wrote
in message news:6A90E70A6F404948991A0380E6793F2F@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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Max Value In Group By Scenario

Eric Pankoke
Thanks.



----- Original Message -----
From: "Igor Tandetnik" <[hidden email]>
To: [hidden email]
Sent: Tuesday, May 5, 2009 8:05:42 AM GMT -05:00 US/Canada Eastern
Subject: Re: [sqlite] Max Value In Group By Scenario

"Eric Pankoke" <[hidden email]> wrote
in message news:6A90E70A6F404948991A0380E6793F2F@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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users