Question 114110 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL – why can't I ORDER BY a function of a column?

I’m trying to get something I thought should be relatively simple (it works in Oracle and MySQL). The PostgreSQL fiddle for the code below is available here – just change the server to check out the others.

Very simple test case:

CREATE TABLE x
(
  y CHAR(1)
);

populate:

INSERT INTO x VALUES ('x');

and

INSERT INTO x VALUES('y');

then (works – as one would expect):

SELECT
  y AS the_char
FROM 
  x
ORDER BY the_char;

Result:

the_char
       x
       y

But then, if I try the following:

SELECT
  y AS the_char
FROM 
  x
ORDER BY ASCII(the_char);

I receive an error:

ERROR:  column "the_char" does not exist
LINE 5: ORDER BY ASCII(the_char);

As mentioned, this works with Oracle and MySQL, but not on PostgreSQL, Firebird and SQL Server.

Can anyone explain why? What is it about a simple function of the column that causes the ORDER BY to fail? This seems to conflict with the manual here which says:

The sort expression(s) can be any expression that would be valid in
the query’s select list. An example is:

SELECT a, b FROM table1 ORDER BY a + b, c;

Tags:

3

Answers


  1. 0

    If you read the documentation more closely, it states the aliases or column output names cannot be used in an expression in order by clause.

    Note that an output column name has to stand alone, that is, it cannot
    be used in an expression

    SELECT a + b AS sum, c FROM table1 ORDER BY sum + c; — wrong

    So, you’re going to need

    SELECT
  y AS the_char
FROM 
  x
ORDER BY ASCII(y);
    Login or Signup to reply.
  2. 0

    Only input columns can be used in expressions other than simple column names, as specified in the documentation:

    Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values.

    Read also this note:

    In the SQL-92 standard, an ORDER BY clause can only use output column names or numbers, while a GROUP BY clause can only use expressions based on input column names. PostgreSQL extends each of these clauses to allow the other choice as well (but it uses the standard’s interpretation if there is ambiguity). PostgreSQL also allows both clauses to specify arbitrary expressions. Note that names appearing in an expression will always be taken as input-column names, not as output-column names.

    Login or Signup to reply.
  3. 0

    An output column name (as the_char) can only be used as a plain value in the ORDER BY clause, but not as part of an expression; y ,on the other hand, can be freely used in an expression for ordering. You can do:

    select y as the_char
from x
order by ascii(y)

    Alternatively, you can use a subquery to change the scope of the output column the_char so it can be managed and processed as a bona fide column. For example:

    select *
from (
  select y as the_char from x
) z
order by ascii(the_char)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search