How to Use an Alias in a Postgresql Order by Clause

How to use an ALIAS in a PostgreSQL ORDER BY clause?

You can always ORDER BY this way:

select 
    title, 
    ( stock_one + stock_two ) as global_stock
from product
order by 2, 1

or wrap it in another SELECT:

SELECT *
from
(
    select 
        title, 
        ( stock_one + stock_two ) as global_stock
    from product
) x
order by (case when global_stock = 0 then 1 else 0 end) desc, title

How to use the sum of two aliases in an order by clause in postgres?

Do it like this:

select * from (
SELECT *, 
    (SELECT COUNT(*) FROM upvote up WHERE up.post_id = post.id) AS upvotes,
    (SELECT COUNT(*) FROM downvote dw WHERE dw.post_id = post.id) AS downvotes
    FROM post) A
ORDER BY (upvotes+downvotes) DESC;

Here is a small DEMO for Postgresql 12.

Casting column alias in ORDER BY clause

This is a big long for a comment.

Here is an early reference to this issue, from which I quote:

ORDER BY (and also GROUP BY) permit references to output column names
only when they are unadorned. You cannot use them in expressions.

If I had to speculate on the reasons for this, it would have to do with expression evaluation. I think the Postgres engine logically rewrites your query to be something like:

SELECT a, b AS c, c::int as INVISIBLEUNSEENCOLUMN
FROM test AS t
ORDER BY t.a, INVISIBLEUNSEENCOLUMN;

That is, it moves the expression to where expressions are usually evaluated (into what I might call the "select" node in the compiled code). Well, this generates the known column error, because that code doesn't understand aliases defined at the same level. When I want to use column aliases in a query, I often use subqueries, to avoid any confusion.

It is easy enough to work around the problem: use a subquery, use the original column name in the order by, or include the cast value in the select.

ORDER BY Alias not working

You can use ORDER BY 1 to order by the first field, which is "Fruits". The same is valid for GROUP BY

Update

For the order, instead of doing the case in the order by, create a new column in.. say.. the second position:

(CASE 
    WHEN "Fruits" = 'Apple' THEN 1 
    WHEN "Fruits" = 'Pear' THEN 2 
    WHEN "Fruits" = 'Grapes' THEN 3 
    ELSE 4 ) as Order

Then in you ORDER BY 2.

Why can't i refer to a column alias in the ORDER BY using CASE?

This has to do with how a SQL dbms resolves ambiguous names.

I haven't yet tracked down this behavior in the SQL standards, but it seems to be consistent across platforms. Here's what's happening.

create table test (
  col_1 integer,
  col_2 integer
);

insert into test (col_1, col_2) values 
(1, 3), 
(2, 2), 
(3, 1);

Alias "col_1" as "col_2", and use the alias in the ORDER BY clause. The dbms resolves "col_2" in the ORDER BY as an alias for "col_1", and sorts by the values in "test"."col_1".

select col_1 as col_2
from test
order by col_2;

col_2
--
1
2
3

Again, alias "col_1" as "col_2", but use an expression in the ORDER BY clause. The dbms resolves "col_2" not as an alias for "col_1", but as the column "test"."col_2". It sorts by the values in "test"."col_2".

select col_1 as col_2
from test
order by (col_2 || '');

col_2
--
3
2
1

So in your case, your query fails because the dbms wants to resolve "NewValue" in the expression as a column name in a base table. But it's not; it's a column alias.

PostgreSQL

This behavior is documented in PostgreSQL in the section Sorting Rows. Their stated rationale is to reduce ambiguity.

Note that an output column name has to stand alone, that is, it cannot be used in an expression — for example, this is not correct:

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

This restriction is made to reduce ambiguity. There is still ambiguity if an ORDER BY item is a simple name that could match either an output column name or a column from the table expression. The output column is used in such cases. This would only cause confusion if you use AS to rename an output column to match some other table column's name.

Documentation error in SQL Server 2008

A slightly different issue with respect to aliases in the ORDER BY clause.

If column names are aliased in the SELECT list, only the alias name can be used in the ORDER BY clause.

Unless I'm insufficiently caffeinated, that's not true at all. This statement sorts by "test"."col_1" in both SQL Server 2008 and SQL Server 2012.

select col_1 as col_2
from test
order by col_1;

How to use the alias of my calculated parameter (in SELECT clause) in WHERE clause with PostgreSQL to prevent repetition?

You can't reuse a select alias in the where clause. You need to repeat the expression, or use a subquery or cte.

For what it's worth, you can use between to have the expression just once instead of twice in the where clause:

SELECT date_part('year', CURRENT_DATE) - f.birth_year AS age
FROM public.foo f
WHERE date_part('year', CURRENT_DATE) - f.birth_year BETWEEN 20 AND 30

As for the subquery solution, that would be:

SELECT *
FROM (
    SELECT date_part('year', CURRENT_DATE) - f.birth_year AS age
    FROM public.foo
) f
WHERE age BETWEEN 20 AND 30

Using calculation with an an aliased column in ORDER BY

The documentation clearly states that:

The column names referenced in the ORDER BY clause must correspond to
either a column or column alias in the select list or to a column
defined in a table specified in the FROM clause without any
ambiguities. If the ORDER BY clause references a column alias from
the select list, the column alias must be used standalone, and not as
a part of some expression in ORDER BY clause:

Technically speaking, your query should work since order by clause is logically evaluated after select clause and it should have access to all expressions declared in select clause. But without looking at having access to the SQL specs I cannot comment whether it is a limitation of SQL Server or the other RDBMS implementing it as a bonus feature.

Anyway, you can use CROSS APPLY as a trick.... it is part of FROM clause so the expressions should be available in all subsequent clauses:

SELECT item
FROM t
CROSS APPLY (SELECT item + '') AS CA(item_for_sort)
ORDER BY item_for_sort

---

Related Topics