My impression from his description was that the actID was a sequential number associated with the crID so in his case he would always know that there would be only one record with a given actID for any given crID which is a foreign key to another table -- and as he wanted all data on that row, select distinct and order by desc by themselves wouldn't have produced what he wanted, because when you select distinct on all columns every row is returned when there aren't any duplicates (as I suspect is the case here).
He could have use cfoutput and grouped by the crID, though there is an easier way to return only the rows he needed. I do agree that in general it's good practice to include a primary key and a in many if not most cases created and modified dates. > 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 s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:207343 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
