Hi

You can't order a recordset by a field alias in SQL.  It you want to order
it by that field, you have to include the whole field source to do it.

E.G. 
SELECT *, count(bob) as countTotal FROM MyTable ORDER BY MyTable - WILL FAIL
SELECT *, count(bob) as countTotal FROM MyTable ORDER BY count(bob) -
WILL/SHOULD WORK   :)
 so try

SELECT
Highschool.Category, Highschool.Region, Highschool.Highschool,
Highschool.Code, (select count (*) from GameScore where (HomeTeam = 
Highschool.Highschool AND HomeTeamScore > AwayTeamScore) OR (AwayTeam = 
Highschool.Highschool AND AwayTeamScore > HomeTeamScore)) as GamesWon,
(select count (*) from GameScore where (HomeTeam = 
Highschool.Highschool AND HomeTeamScore < AwayTeamScore) OR (AwayTeam = 
Highschool.Highschool AND AwayTeamScore < HomeTeamScore)) as GamesLost
FROM Highschool WHERE Highschool.Category = 'AAAA' AND Play = yes 
ORDER BY (select count (*) from GameScore where (HomeTeam = 
Highschool.Highschool AND HomeTeamScore > AwayTeamScore) OR (AwayTeam = 
Highschool.Highschool AND AwayTeamScore > HomeTeamScore))

Good luck
Mark

> -----Original Message-----
> From: Valerie L. Criswell [mailto:valerie(at)suresource.net]
> Subject: SELECT Statement Blues
> 
 It seems that because the win column is a calculated 
> one generated on
> the fly, when I try to ORDER BY it, I get an error.  Does 
> anyone know how to
> overcome this problem?  The site is written in Cold Fusion 
> and the database
> is Access2000.  Below is the SELECT statement ordering by 
> highschool only.
> Below, I have also included the CF error that I receive when 
> I ORDER BY
> GamesWon.  To help get an idea of what this is all about, the 
> live page may
> be viewed at 
> http://www.schighschoolsports.com/sports/mbball/index.cfm .
> TIA to all! ~Val
> 

• Web Designer Courses
• Off the Shelf Training Courses

HWG hwg-techniques mailing list archives, maintained by Webmasters @ IWA