Yes, I did several attempts at a derived table, but I still ran into the same problem... I couldn't do a top 1 with order by datetime_created desc because I am reporting across multiple orders, not just one. I need to group by something, but if it is just the order_num I can't get the product_name, and if I group by the product_name as well, it itemizes all the products.
~Brad -----Original Message----- From: Dawson, Michael [mailto:[EMAIL PROTECTED] Sent: Friday, September 28, 2007 11:54 AM To: CF-Talk Subject: RE: SQL Question 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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Get the answers you are looking for on the ColdFusion Labs Forum direct from active programmers and developers. http://www.adobe.com/cfusion/webforums/forum/categories.cfm?forumid-72&catid=648 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289756 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4