For those follwoing this thread:
I was led to this solution by Matt... this is the final form used in the
application so the table names are not the same but close if you want to
inspect the answer. The JOIN (...)PBinD2 is on a temp result set. He
referred to this as an In-Line View that was joined.
SELECT PBin.Name, Device.Lot_Num, SUM(PBinD.Qty) AS Qty
FROM PBinD
INNER JOIN Serial ON PBinD.Serial_ID = Serial.Serial_ID
INNER JOIN Device ON Serial.Device_ID = Device.Device_ID
INNER JOIN PBin ON PBinD.PBin_ID = PBin.PBin_ID
INNER JOIN (
SELECT MAX(History_ID) AS MaxHistID
FROM PBinD
INNER JOIN Serial ON PBinD.Serial_ID =
Serial.Serial_ID
INNER JOIN Device ON Serial.Device_ID =
Device.Device_ID
Group By Device.Lot_Num
) PBinD2 ON PBinD.History_ID = PBinD2.MaxHistID
WHERE Device.Device_ID = #SomeLoopingVariable#
GROUP BY PBin.SEQ, PBin.Name, Device.Lot_Num
On Jan 9, 2008 3:30 PM, Gonzo Rock <[EMAIL PROTECTED]> wrote:
> Yes!
>
>
> http://dev.mysql.com/doc/refman/6.0/en/example-maximum-column-group-row.html
>
> That is the sql that Matt Williams suggested to me offline earlier this
> morning and I have been trying to wrap my head around it... as Brad
> described earlier in this thread ;-) And studying what Dominic has
> suggested for nuggets.
>
> On Jan 9, 2008 11:45 AM, Jochem van Dieten <[EMAIL PROTECTED]>
> wrote:
>
> > Gonzo Rock wrote:
> > > 1st task - Given a Lot_ID, query the most recent BinD_Quantities
> > recorded
> > > for each of the Serial_ID's in the Lot.
> >
> > The MySQL manual has a section named "Groupwise Maximum". It presents 2
> > solutions, one of which will also work on other databases.
> >
> > Jochem
> >
> >
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296328
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
