How to use an ALIAS in a PostgreSQL ORDER BY claus

Question

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!

Answer 1

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

Answer 2

On solution is to use the position:

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

However, the alias should work, but not necessarily the expression. What do you mean by "global_stock = 0"? Do you mean the following:

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