Re: Ordering Error

Jason Davis <jasdavis(at)tassie(dot)net(dot)au> writes:
> radius=# SELECT col1 AS testing FROM test ORDER BY lower(testing) ASC;
> ERROR: Attribute "testing" not found

You can't do that --- you have to write lower(col1) --- and if col1 is
actually a complicated expression, too bad, you have to duplicate it.

This is stated in the fine print in the SELECT reference page:

: An ORDER BY item can be the name or ordinal number of an output column
: (SELECT expression), or it can be an arbitrary expression formed from
: input-column values. In case of ambiguity, an "ORDER BY name" will be
: interpreted as an output-column name.

If this seems slightly bizarre, well, it is. It's a compromise between
obeying the restrictive SQL92 spec (which says "output column name or
number") and the more reasonable but thoroughly incompatible SQL99 spec
(which says "expression in the input columns").

If duplicating the expression seems unreasonable, you can try this
locution:

SELECT * FROM
(SELECT col1 AS testing FROM test) AS ss
ORDER BY lower(testing) ASC;

so that "testing" is an input column name as far as the outer SELECT
is concerned. Be aware however that this is only a notational savings
--- the system will likely "flatten" the subselect, ending up with two
copies of the col1 expression at runtime anyway.

regards, tom lane