I meant order descending by actid too, sorry. But maybe im misunderstanding
your data structure since others see it a different way.
Another... way group your cfouput...
<!--- THIS WILL CREATE THE SAMPLE DATA YOU USED --->
<cfset q = querynew("crID,actID,description,owner")>
<cfset queryaddrow(q)>
<cfset querysetcell(q, "crid", 1)>
<cfset querysetcell(q, "actid", 1)>
<cfset querysetcell(q, "description", "Test")>
<cfset querysetcell(q, "owner", 444)>
<cfset queryaddrow(q)>
<cfset querysetcell(q, "crid", 1)>
<cfset querysetcell(q, "actid", 2)>
<cfset querysetcell(q, "description", "Test Update")>
<cfset querysetcell(q, "owner", 124)>
<cfset queryaddrow(q)>
<cfset querysetcell(q, "crid", 2)>
<cfset querysetcell(q, "actid", 1)>
<cfset querysetcell(q, "description", "Test")>
<cfset querysetcell(q, "owner", 578)>
<cfdump var="#q#">
<!--- END SAMPLE DATA ---------->
<!--- get all the records in order by crid with their highest actid first
--->
<cfquery name="rs" dbtype="query">
select * from q
order by crid, actid desc
</cfquery>
<!--- this will display exactly what was asked for --->
<!--- distinct crids with the highest actid of that crid --->
<pre>
crID actID description owner
<cfoutput query="rs" group="crid">#crid# #actID# #description#
#owner#
</cfoutput>
</pre>
-----Original Message-----
From: Ewok [mailto:[EMAIL PROTECTED]
Sent: Friday, May 20, 2005 10:40 PM
To: CF-Talk
Subject: RE: SQL Query Question
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:207348
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
