How Can I Use an Alias in a PostgreSQL ORDER BY Clause Without Getting a 'column does not exist' Error?

Using an ALIAS in PostgreSQL ORDER BY Clause

When attempting to utilize an alias in the ORDER BY clause of a PostgreSQL query, some users encounter the error, "column does not exist."

Issue Explanation

Consider the following example:

Running this query in PostgreSQL 8.1.23 yields the error: "column 'global_stock' does not exist." This error occurs because aliases are not recognized in the ORDER BY clause.

Solution

There are two possible solutions:

1. Numerical Ordering:

Instead of using the alias, you can reference the column by its position in the SELECT clause. In the above example, the 'global_stock' alias corresponds to column 2. Therefore, the modified query would be:

2. Subquery with CASE Expression:

Another option is to use a subquery and a CASE expression:

In this case, the CASE expression assigns a value of 1 to rows with 'global_stock' = 0, and 0 to all others. The query then sorts the results in descending order based on this value, effectively prioritizing available items.

The above is the detailed content of How Can I Use an Alias in a PostgreSQL ORDER BY Clause Without Getting a 'column does not exist' Error?. For more information, please follow other related articles on the PHP Chinese website!