Have you tried using a derived table? I think that should work.
SELECT *
FROM t1
INNER JOIN (SELECT order_num, max(datetime_created) as latestdate
FROM t1) AS t2
ON t1.order_num = t2.order_num
AND t1.datetime_created = t2.datetime_created
M!ke
-----Original Message-----
From: Brad Wood [mailto:[EMAIL PROTECTED]
Sent: Friday, September 28, 2007 11:46 AM
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Create robust enterprise, web RIAs.
Upgrade to ColdFusion 8 and integrate with Adobe Flex
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289752
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
