Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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!

like image 664
Marcio Mazzucato Avatar asked Aug 02 '12 20:08

Marcio Mazzucato


People also ask

Can I use alias in ORDER BY clause?

Yes, you can certainly use column aliases in your "order by" clause.

How do I create an alias in PostgreSQL?

Column AliasSELECT column_name AS alias_name FROM table; or, SELECT column_name alias_name FROM table; Below Syntax is for column alias used with expressions: SELECT expression alias_name FROM table; The primary use of column alias is to make the output of a query more meaningful.

How do I use alias in SQL SELECT statement?

Alias is used to give a temporary name(only for the duration of the query) to the column or table in order to make the column name or table name more readable. It does not change the name of the column permanently. Alias can be performed using the 'AS' keyword or without any keyword.

Can we use alias name in WHERE clause in SQL Server?

In PROC SQL, a column alias can be used in a WHERE clause, ON clause, GROUP BY clause, HAVING clause, or ORDER BY clause. In the ANSI SQL standard and ISO SQL standard, the value that is associated with a column alias does not need to be available until the ORDER BY clause is executed.


1 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.

like image 170
Follpvosten Avatar answered Nov 13 '22 19:11

Follpvosten