This solution is similar to Greg's in that I will get dupes if more than one product is added at the same time.
Essentially, it is the same logic as my original 3rd option, except for it attempts to do it in one statement. I think the best way may in fact be to get a distinct list of orders in a temp table with their max product's datetime_created, and then perform a second update statement on order_num and datetime_created to get the product name. That would eliminate my dupes. I'm still hitting everything twice-- once with a grouped select, and once with an update, but maybe that's the best way. Joining on a date kind of scared me. It seemed like there should have been a way to get the corresponding primary_key (which has a clustered index) and use that for the join. ~Brad -----Original Message----- From: Andrew Clark [mailto:[EMAIL PROTECTED] Sent: Friday, September 28, 2007 11:58 AM To: CF-Talk Subject: RE: SQL Question Just move the aggregate up to the from and do a join: select * from t myT, (select order_num, max(datetime_created) as max_dt_created from t group by order_num) xxxx where myT.order_num = xxxx.order_num AND myT.datetime_created = xxxx.max_dt_created Regardless of how you do this, you first have to determine the max datetime_create, so you're going to have to use a subselect I think. -- Andrew ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:289759 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
