Jeff Chastain wrote:
>
> I have a table that looks something like this ....
>
> crID actID description owner
> 1 1 Test 444
> 1 2 Test Update 124
> 2 1 Test 578
>
> Now, what I need is a listing of all distinct crID records, where the actID
> is the greatest. Basically, this is a history setup. There are a series of
> change requests (crID) that each have 1 or more actions (actID). I need a
> snapshot of the most recent status of each change request ( max(actID) ).
> The problem I am having is with the aggregate functions and getting all of
> the rest of the fields at the same time. The following query returns the
> correct crID / actID combination, but how do I get the other fields?
>
> SELECT crID, MAX(actID) AS actID
> FROM test
> GROUP BY crID
By joining this result to the original table:
SELECT a.*
FROM test a NATURAL JOIN (
SELECT crID, MAX(actID) AS actID
FROM test
GROUP BY crID) b
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207269
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
