You can do this? ORDER BY BinD.Transaction_ID DESC, Bin.Bin_Name ASC
I will have to experiment with that. I did not know this was possible to ORDER each joined table a different way. -----Original Message----- From: Sonny Savage [mailto:[EMAIL PROTECTED] Sent: Friday, January 11, 2008 10:02 AM To: CF-Talk Subject: Re: A Very Sophisticated Query *Correction on #2* SELECT BinD.Transaction_ID, BinD.Bin_ID, Bin.Bin_Name, BinD.Serial_ID, Serial.Serial_Name, BinD.Bin_Quantity FROM BinD INNER JOIN Bin ON BinD.Bin_ID = Bin.Bin_ID INNER JOIN Serial ON BinD.Serial_ID = Serial.Serial_ID WHERE BinD.Serial_ID = 2 ORDER BY BinD.Transaction_ID DESC, Bin.Bin_Name ASC On Jan 11, 2008 10:01 AM, Sonny Savage <[EMAIL PROTECTED]> wrote: > Tell me if this is what you're looking for: > > 1) *BinD Records For a Transaction, most recent BinD first:* > SELECT BinD.BinD_ID, > BinD.Bin_ID, > Bin.Bin_Name, > BinD.Serial_ID, > Serial.Serial_Name , > BinD.Bin_Quantity > FROM BinD > INNER JOIN Bin ON BinD.Bin_ID = Bin.Bin_ID > INNER JOIN Serial ON BinD.Serial_ID = Serial.Serial_ID > WHERE BinD.Transaction_ID = 63 > ORDER BY BinD.BinD_ID DESC > > 2) *BindD Records For a Serial, most recent transactions first, the sorted > by Bin:* > SELECT BinD.Transaction_ID, > BinD.Bin_ID, > Bin.Bin_Name, > BinD.Serial_ID, > Serial.Serial_Name, > BinD.Bin_Quantity > FROM BinD > INNER JOIN Bin ON BinD.Bin_ID = Bin.Bin_ID > INNER JOIN Serial ON BinD.Serial_ID = Serial.Serial_ID > WHERE BinD.Serial_ID = 2 > ORDER BY BinD.Transaction_ID DESC > > > On Jan 9, 2008 2:25 AM, Gonzo Rock <[EMAIL PROTECTED]> wrote: > > > I know there are a few SQL savants on this list so I wanted to see if I > > might learn something from one of you. I believe this is a very > > sophisticated query... at least for me it is .... A real head scratcher > > ;-) And I really need to figure this out! > > > > So here goes... > > There is some Lot of parts being manufactured ... so we have the Lot > > Table. > > > > Each part in the Lot has a unique Serial ID to identify it in the Lot it > > belongs to... so we have the Serial Table. > > > > Each unique serialized part is broken up into multiple pieces and each > > piece receives a Bin designation for it's Grade so we have a Bin Table. > > > > The pieces are grades several time... and every time the Lot is Graded > > the > > results for each Serialized part in the Lot are stored in the BinD (D > > for > > Detail), table and they all share the same incrementing Transaction_ID > > for > > when the Grading was done. > > > > Now the task is two fold. > > > > 1st task - Given a Lot_ID, query the most recent BinD_Quantities > > recorded > > for each of the Serial_ID's in the Lot. > > > > I've been trying to do this by creating loops inside of loops inside of > > loops, each one running a query at the center of each respective loop. > > > > IE Query Lots, > > Loop through Lots and query Serials, > > Loop through Serials and query BinD records. > > > > But heres the rub(at least for me ;-).... How to query the most recent > > set > > of BinD records? Or better yet a single query that pulls the results > > of > > all the serials for a Trans_ID broken out by Bin_ID, Summed Bin > > Quantity. > > > > 2nd task - Given a Serial_ID, generate a table listing showing the > > various > > values the Bins held in the past up to the most recent Grading...(ie, > > the > > serial with the highest Transaction_ID) > > > > Lot Table > > Lot_ID, Dev_Name > > ============== > > 1, 2001 > > 2, 2001-A > > 3, 20012 > > > > Serial Table > > Serial_ID, Serial_Name, Lot_ID (relates to the Lot Table) > > ============== > > 1, 1003204, 2 > > 2, 1003205, 2 > > 3, 1003250, 2 > > 4, 1001231, 1 > > 5, 100121, 1 > > > > Bin Table > > Bin_ID, Bin_Name > > ============== > > 1, Grade A > > 2, Grade B > > 3, Grade C > > 4, Grade D > > > > BinD Detail Table > > BinD_ID, Bin_ID, Serial_ID, Bin_Quantity, Transaction_ID(of the test > > that > > generated Group results) > > ============== > > 1,1,1,23,31 > > 2,2,1,15,31 > > 3,3,1,14,31 > > 4,4,1,20,31 > > 5,1,2,13,31 > > 6,2,2,13,31 > > 7,3,2,20,31 > > 8,4,2,13,31 > > 9,1,3,23,31 > > 10,2,3,191 > > 11,3,3,20,31 > > 12,4,3,13,31 > > 13,1,1,23,63 > > 14,2,1,15,63 > > 15,3,1,14,63 > > 16,4,1,20,63 > > 17,1,2,13,63 > > 18,2,2,13,63 > > 19,3,2,20,63 > > 20,4,2,13,63 > > 21,1,3,23,63 > > 22,2,3,19,63 > > 23,3,3,20,63 > > 24,4,3,13,63 > > 25,1,4,12,67 > > 26,2,4,0,67 > > 27,3,4,14,67 > > 28,4,4,17,67 > > 29,1,5,9,67 > > 30,2,5,10,67 > > 31,3,5,24,67 > > 31,4,5,18,67 > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:296420 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
