You need to use a sub select to the order by.
Select ID, (Select ID, SUM(amount) from contestant group by ID)
from contestant
order by summed amount
Been a while since I have done something like that but that is the general
gist.
Thank You,
Peter
Peter J. MacDonald II
Creative Computing, Inc.
100 Middle Street
Lincoln, RI 02865
Phone: 401.727.0183 x123
Fax: 401.727.4998
Portable: 401.965.3661
E-MAIL: [EMAIL PROTECTED]
Web Page: www.creatcomp.com
-----Original Message-----
From: Jay Patton [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, February 07, 2001 14:00
To: CF-Talk
Subject: ORDER BY?
im having trouble getting this query to order the results the way that i
want it to...
OR am i going about this the wrong way? i want it to total the points for
each contestant
and then output them in a page listed first through whatever it outputs them
fine w/o the order by
but it orders them by ContestantID, i need it go order by the amount of
total points that the query
comes up with. it should be:
contestant 2 500
contestant 3 485
contestant 1 355
ect....
but instead its
contestant 1 355
contestant 2 500
contestant 3 485
ect..
can anyone help me with this?
here is my query:
<!---
<cfquery name="GetTotalPts"
datasource="?????"
dbtype="ODBC">
SELECT ContestantID, SUM (AdjPoints) as TOTALPTS, SUM (MoneyEarned) as
TOTALMONEY
FROM ResultsManager
GROUP BY ContestantID
ORDER BY ??????? <--- CANT FIGURE OUT WHAT TO PUT HERE IVE TRIED TOTALPTS
but it says its not a valid field in the table... so im at a loss now.
</cfquery>
-->
Thanks,
Jay Patton
Web Pro USA
406.549.3337 ext. 203
1.888.5WEBPRO
www.webpro-usa.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists