Use distinct and order descending by crid. In the future, I highly recommend a unique key (PK) or at least a date/time field to decide which are the latest records
-----Original Message----- From: Jeff Chastain [mailto:[EMAIL PROTECTED] Sent: Friday, May 20, 2005 9:59 AM To: CF-Talk Subject: OT: SQL Query Question 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:207328 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