Menu
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!
664
Yes, you can certainly use column aliases in your "order by" clause.
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.
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.
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.
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
ASclause.
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.
170
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
