How to Properly Use Aliases in PostgreSQL's ORDER BY Clause?

Ordering by Aliases in PostgreSQL ORDER BY Clause

In PostgreSQL, using aliases in an ORDER BY clause can be tricky. Consider the following query:

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

When executing this query in PostgreSQL 8.1.23, an error is encountered: "ERROR: column "global_stock" does not exist". To resolve this, there are two primary approaches:

Using Column Number

The first approach is to order by the column number instead of the alias. In this case, the query would look like this:

SELECT 
    title, 
    (stock_one + stock_two) AS global_stock
FROM
    product
ORDER BY
    2, 1;

Here, "2" refers to the second column, which is the alias "global_stock".

Using a Subquery

An alternative approach is to wrap the original query in a subquery and then order by the alias within the subquery. The query would become:

SELECT *
FROM
(
    SELECT 
        title, 
        (stock_one + stock_two) AS global_stock
    FROM 
        product
) AS x
ORDER BY 
    (CASE WHEN global_stock = 0 THEN 1 ELSE 0 END) DESC,
    title;

In this case, the alias "global_stock" is used within the subquery, and the ORDER BY clause is applied to the result set of the subquery.

The above is the detailed content of How to Properly Use Aliases in PostgreSQL's ORDER BY Clause?. For more information, please follow other related articles on the PHP Chinese website!