Re: [sqlite] Max Value In Group By Scenario

[epankoke]

Thanks. 



- Original Message - 
From: "Igor Tandetnik"  
To: sqlite-users@sqlite.org 
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"  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 
sqlite-users@sqlite.org 
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Max Value In Group By Scenario

[Igor Tandetnik]

"Eric Pankoke"  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users