Chad, that means order by Transaction_ID descending FIRST, then order by Bin_Name ascending SECOND.
So where there are rows with the same Transaction_ID, the order will be by Bin_Name. On Jan 11, 2008 9:39 AM, Chad Gray <[EMAIL PROTECTED]> wrote: > 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:296422 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
