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
-----Original Message-----
From: Brad Wood [mailto:[EMAIL PROTECTED]
Sent: Friday, September 28, 2007 12:11 PM
To: CF-Talk
Subject: SOT: SQL Question
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Check out the new features and enhancements in the
latest product release - download the "What's New PDF" now
http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289749
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
