Thread: Ordering Error

Hi all

Searched the docs, archives etc. but can't find any reference to this
problem I'm having with ORDER BY used in conjunction with SELECT...AS.

A simplified demonstration:

radius=# SELECT col1 AS testing FROM test ORDER BY testing ASC;
  testing
----------
  Ordering
  Test
  This
  an
  is
(5 rows)

But I need the lowercased ordering result for this column for a
case-insensitivity listing, however I get

radius=# SELECT col1 AS testing FROM test ORDER BY lower(testing) ASC;
ERROR:  Attribute "testing" not found

Using the ordinal notation (ie. ORDER BY lower(1) ASC) gives weird results
(in my original query).

I can't use ORDER BY lower(col1) in my original and more complex query as
the column is the result of a function.

What obviousness would I be missing here?

version = 7.3.2

cheers
Jason

Am Son, 2003-08-10 um 15.23 schrieb Jason Davis:

> I can't use ORDER BY lower(col1) in my original and more complex query as
> the column is the result of a function.

If col1 is a function then try
ORDER BY <here_the_function_again_to_avoid_alias>

Of course this is ugly and slow, but if you find no other way at all ...

--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de

Jason Davis <jasdavis@tassie.net.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