why do you have to use a single select statement? Thats pretty limiting there..
On 9/28/07, Brad Wood <[EMAIL PROTECTED]> wrote: > This one's been bugging me for a day now. I don't think this is > possible, but before I gave up I thought I would ask. (Yes Rick, I > googled it first) > > As usual, the real scenario is much more complicated, but this is a > simple example that shows the concept. > > Let's say I had a table with multiple products per order and the date > they were ordered: > > order_num product_name datetime_created > 1 apples 9/1/2007 > 1 oranges 9/10/2007 > 1 bananas 9/20/2007 > 2 apples 9/5/2007 > 2 pears 9/15/2007 > 2 kiwi 9/25/2007 > > Is it possible with a SINGLE select statement to simply get a distinct > list of orders represented with the LAST product ordered like so: > > order_num product_name datetime_created > 1 bananas 9/20/2007 > 2 kiwi 9/25/2007 > > The knee jerk reaction is to group by order_num, and then use the max > aggregate on datetime_created. That's fine, but then you can't get the > corresponding product_name. To get the product name in the select list > you have to add it in the group by which then itemizes all the products > and you no longer have a distinct list of orders. > > Using a derived table doesn't even help. I can't do "top 1" with an > "order by datetime_ordered desc" because I am reporting across multiple > orders. > > I am on MS SQL Server 2005. The only ways I can find to do this are: > > 1) Cursor or while loop over orders getting last product row by > agonizing row > > 2) Create function to return last product on the order, and "cross > apply" it. The function still has to run RBAR for every order though. > > 3) Initial select into a temp table with max(datetime_created). Update > temp table in a second pass with product_name joining on > datetime_created. I don't like joining to a date because it guaranteed > unique. > > Ideas? > > Thanks. > > ~Brad > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion is delivering applications solutions at at top companies around the world in government. Find out how and where now http://www.adobe.com/cfusion/showcase/index.cfm?event=finder&productID=1522&loc=en_us Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289748 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
