This should be pretty simple actually =)
SELECT DISTINCT
Order_num,
datetime_created,
(SELECT product_name FROM tableName WHERE order_num = a.ordernum
AND record_ID = (SELECT MAX(record_ID) FROM tableName WHERE order_num =
a.order_num)) as LastProduct
FROM
tableName A
This assumes you have some type of auto-incrementing primary key, but
you should get the idea. Let me know if this works for you, I tested it
in SQL 2005 no sweat.
Chris Peterson
Gainey IT
Adobe Certified Advanced Coldfusion Developer
-----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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:289762
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
