Sorry for the off-topic, but I have been beating on this one for a while and
the only answer I can find is really, really ugly. So, hopefully somebody
knows more about queries that I do and can show me the error of my ways ....
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
The only thing I have come up with thus far is having to run another query
for each record returned in the above query in order to get the additional
details and a query per row just can't be a good thing.
The resulting data set that I am looking for would be as follows ....
crID actID description owner
1 2 Test Update 124
2 1 Test 578
Any pointers would be greatly appreciated and would save the few brain cells
I have left.
Thanks
-- Jeff
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207252
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=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
