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

user_999820 0
Viewed 12717

I have the following query:

SELECT 
    title, 
    (stock_one + stock_two) AS global_stock
FROM
    product
ORDER BY
    global_stock = 0,
    title;

Running it in PostgreSQL 8.1.23 i get this error:

Query failed: ERROR: column "global_stock" does not exist

Anybody can help me to put it to work? I need the availale items first, after them the unnavailable items. Many thanks!

postgresql-8.1 postgresql sql
3 Answers

In case anyone finds this when googling for whether you can just ORDER BY my_alias: Yes, you can. This cost me a couple hours.

As the postgres docs state:

The ordinal number refers to the ordinal (left-to-right) position of the output column. This feature makes it possible to define an ordering on the basis of a column that does not have a unique name. This is never absolutely necessary because it is always possible to assign a name to an output column using the AS clause.

So either this has been fixed since, or this question is specifically about the ORDER BY my_alias = 0, other_column syntax which I didn't actually need.

user_92616460
Related