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

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!

@a_horse_with_no_name, I am trying, my host has only this version at now. Thanks for your suggestion! — Marcio Mazzucato, Aug 03 '12 at 01:32
Then you should change your host. 8.1 has been out of support for over 2 years now. — , Aug 03 '12 at 07:00
@a_horse_with_no_name, I will try, by the way, do you have a suggestion of a good host with a Postgres updated? Now i am using [Bounceweb (Brazil)](http://www.bounceweb.com.br/) — Marcio Mazzucato, Aug 03 '12 at 13:38

score 23 · Accepted Answer · answered Aug 02 '12 at 20:59

23

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

answered Aug 02 '12 at 20:59

Taryn

242,637
56
362
405

You saved my day! I've used the first option, it will be easier to me! Thank you very much! – Marcio Mazzucato Aug 02 '12 at 21:01
Can you tell me please if the second option is much slower than the first one? I am having a problem with the pagination using the first solution, because i am using the LIMIT clause. – Marcio Mazzucato Aug 06 '12 at 20:58
Dirty trick (the order by 2,1) but if it works, it works :) thanks a ton – Eon Feb 25 '14 at 14:04
2

@Noobgrammer Yuck, this is a really old answer and I wouldn't suggest to `order by 2, 1` any longer. :) – Taryn Feb 25 '14 at 14:06
@bluefeet what do you suggest instead? – felixfbecker May 12 '16 at 19:02
@felixfbecker What are you talking about? – Taryn May 12 '16 at 19:03
Would doing `ORDER BY COALESCE(2, 1)` according to [this answer](http://stackoverflow.com/a/20028142/242933) work? – ma11hew28 Aug 30 '16 at 01:13
@mattdipasquale You'd probably need to test it as I'm not sure offhand. I'll try to test it tomorrow if I can get my hands on a server to test on. – Taryn Aug 30 '16 at 01:16
2

@bluefeet OK. Thank you. I don't think it'd work anyway. I think `COALESCE(2, 1)` would always evaluate to `2`, making it equivalent to `ORDER BY 2`. Anyway, never mind. In my case, I think it actually works better to just add `COALESCE(MAX(c.created), p.created) AS posted_at` to the list of columns after `SELECT` and then `ORDER BY posted_at DESC`. – ma11hew28 Aug 30 '16 at 01:32
@taryn Thanks! I need to do ORDER BY LOWER(2), but PostGres does not seem to interpret the 2 as a column position when it is passed to a function like this, just interprets as integer 2. Any ideas how to force it to? I realize I could add LOWER(exp) to the SELECT clause, but changing the SELECT clause is undesired in my use case. – bradw2k Oct 08 '20 at 17:11

score 5 · Answer 2 · answered Apr 21 '21 at 10:34

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.

score 4 · Answer 3 · edited Feb 10 '19 at 08:46

One 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

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

3 Answers3

Linked

Related