CODEWITHSUNDEEP
postgresqlsql-order-by
Jivan
Jivan

Reputation: 23068

Product of regular field and computed field in an ORDER BY clause

The following query works fine:

SELECT a, b, c,
(SELECT COUNT(*) AS COUNT FROM table_b WHERE field_a = p.a) AS d
FROM table_a AS p
ORDER BY b DESC

And this also works:

SELECT a, b, c,
(SELECT COUNT(*) AS COUNT FROM table_b WHERE field_a = p.a) AS d
FROM table_a AS p
ORDER BY d DESC

But the following produces a ERROR; column 'd' does not exist error:

SELECT a, b, c,
(SELECT COUNT(*) AS COUNT FROM table_b WHERE field_a = p.a) AS d
FROM table_a AS p
ORDER BY (b * d) DESC

Only difference between the three queries above is the ORDER BY clause. In the first two queries, results are ordered by either the b field or by the dynamic d field. In the last query, results are (should be) ordered by the product of b times d.

How comes, in the last query, PostgreSQL says that d does not exist while it can find it without issue in the second query?

Upvotes: 1

Views: 25

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

Arbitrary expressions in the order by clause can only be formed from input columns:

Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values.

You will need to subquery it:

select *
from (select 1 as a, 2 as b) s
order by a * b

Upvotes: 1

Related Questions