select t.order_num, t.product_name, t.datetime_created from test t, ( select order_num, max(datetime_created) maxdt from test group by order_num ) t2 where t2.order_num = t.order_num and t2.maxdt = t.datetime_created
1 query, but 2 selects = gets what you want On 9/28/07, Greg Morphis <[EMAIL PROTECTED]> wrote: > 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 > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Enterprise web applications, build robust, secure scalable apps today - Try it now ColdFusion Today ColdFusion 8 beta - Build next generation apps Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289751 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
