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 -----Original Message----- From: Brad Wood [mailto:[EMAIL PROTECTED] Sent: Friday, September 28, 2007 12:46 PM To: CF-Talk Subject: RE: SQL Question Doesn't look like MS SQL Server 2005 will let me compare more than one column in a where clause. Even if it did, this requires two selects. My understanding is the sub-select would get ran once for every record in table t. In my case that's 11 Millions times! ~Brad -----Original Message----- From: Andrew Clark [mailto:[EMAIL PROTECTED] Sent: Friday, September 28, 2007 11:33 AM To: CF-Talk Subject: RE: SQL Question Off the top of my head I get: select * from t myT where (order_num, datetime_created) = (select order_num, max(datetime_created) from t where order_num = myT.order_num group by order_num) Note: I tested this in postgres, not sql server... -- 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:289754 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
