'Order By' an 'as' field

[ArtNirvana]

I have several working this way in my code:

<cfquery name="GetSignups" datasource="Weddingsetgo">
select count(showcasesignups.userid) as Registered,sum(attended) as
ShowedUp,users.firstname,users.lastname
from showcasesignups,users
where users.userid=showcasesignups.userid
group by users.userid,users.firstname,users.lastname
order by <cfoutput>#registered#<cfoutput>
</cfquery>

[ArtNirvana]

Sorry, I completely was not thinking and typed away - here is actual example
that would actuall work:

<cfquery username="#request.dbuser#" password="#request.dbpass#"
datasource="#attributes.DSN#" name="checklogin">
SELECT login
FROM Logins
Where login = '#Trim(login)#'<cfif EditAdmin IS "Edit"> and Login_ID <>
#Login_ID#</cfif>
</cfquery>

[ctrl+alt+delete]

What database are you using?

"Explorer5" <andrew@alconic-inc.com> wrote in message
news:cutnls$7j5$1@forums.macromedia.com...

> Is it possible to run a query in which the 'order by' function can use a
> variable created by an 'as' function? I keep getting an error saying there
> is
> no field by that name.. See code below..
>
> <cfquery name="GetSignups" datasource="Weddingsetgo">
> select count(showcasesignups.userid) as Registered,sum(attended) as
> ShowedUp,users.firstname,users.lastname
> from showcasesignups,users
> where users.userid=showcasesignups.userid
> group by users.userid,users.firstname,users.lastname
> order by registered
> </cfquery>
>

[ctrl+alt+delete]

Uh, you are wrong... at least when talking about MS SQL Server.

"Jochem van Dieten - TMM" <nomail@devnull.invalid> wrote in message
news:cuto5t$811$1@forums.macromedia.com...

> Explorer5 wrote:

>> Is it possible to run a query in which the 'order by' function can use a
>> variable created by an 'as' function?

>
> It is not allowed in SQL.
>
>

>> <cfquery name="GetSignups" datasource="Weddingsetgo">
>> select count(showcasesignups.userid) as Registered,sum(attended) as
>> ShowedUp,users.firstname,users.lastname
>> from showcasesignups,users
>> where users.userid=showcasesignups.userid
>> group by users.userid,users.firstname,users.lastname
>> order by registered
>> </cfquery>

>
>
> select
> count(showcasesignups.userid) as Registered,
> sum(attended) as ShowedUp,
> users.firstname,
> users.lastname
> from
> showcasesignups,
> users
> where
> users.userid=showcasesignups.userid
> group by
> users.userid,
> users.firstname,
> users.lastname
> order by
> count(showcasesignups.userid)
>
> Jochem
>
> --
> Jochem van Dieten
> Team Macromedia Volunteer for ColdFusion, beer and fun.

[Jochem van Dieten - TMM]

ctrl+alt+delete wrote:

> Uh, you are wrong... at least when talking about MS SQL Server.

I am not.

I am writing about SQL. Column aliasses can only be used in a
HAVING, not in an ORDER BY, GROUP BY or WHERE. That is because if
that were allowed the following would be undefined:

CREATE TABLE test (ID INTEGER, test TEXT);

SELECT ID AS test
FROM test
ORDER BY test

If sorting by an alias were allowed, should the sort happen by
the alias or by the real field? If Microsoft chooses to put
unpredictable behaviour in their products that is their problem,
but it is not allowed by the standard. Get a copy of ISO/IEC
9075-2:2003 and see for yourself.

Jochem

--
Jochem van Dieten
Team Macromedia Volunteer for ColdFusion, beer and fun.

[ctrl+alt+delete]

I am not going to debate whether or not it's unpredictable, it's valid for
MS SQL server. And the example you sited is pretty straight forward despite
your intent to create ambiguity. You are selecting and ordering the result
set by the column 'test'.

"Jochem van Dieten - TMM" <nomail@devnull.invalid> wrote in message
news:cv06ao$t70$1@forums.macromedia.com...

> ctrl+alt+delete wrote:

>> Uh, you are wrong... at least when talking about MS SQL Server.

>
> I am not.
>
> I am writing about SQL. Column aliasses can only be used in a HAVING, not
> in an ORDER BY, GROUP BY or WHERE. That is because if that were allowed
> the following would be undefined:
>
> CREATE TABLE test (ID INTEGER, test TEXT);
>
> SELECT ID AS test
> FROM test
> ORDER BY test
>
> If sorting by an alias were allowed, should the sort happen by the alias
> or by the real field? If Microsoft chooses to put unpredictable behaviour
> in their products that is their problem, but it is not allowed by the
> standard. Get a copy of ISO/IEC 9075-2:2003 and see for yourself.
>
> Jochem
>
> --
> Jochem van Dieten
> Team Macromedia Volunteer for ColdFusion, beer and fun.