documentation extension request - order with function over aggregated functions
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/9.5/queries-order.html
Description:
In https://www.postgresql.org/docs/9.5/queries-order.html it is said that
the following is intentionally not allowed:
```
SELECT a + b AS sum, c FROM table1 ORDER BY sum + c; -- wrong
```
It took me a while to figure out how to do it properly so I propose to add
the following (or similar) info:
---
If you need to sort by this kind of expression, you will have to retrieve an
extra column with the required computation and use it as a sorting key:
```
SELECT a + b AS sum, c FROM table1, a + b + c AS my sorting_key ORDER BY
my_sorting_key; -- correct
```
On Tue, 2020-02-04 at 08:59 +0000, PG Doc comments form wrote:
SELECT a + b AS sum, c FROM table1 ORDER BY sum + c; -- wrong
It took me a while to figure out how to do it properly so I propose to add
the following (or similar) info:---
If you need to sort by this kind of expression, you will have to retrieve an
extra column with the required computation and use it as a sorting key:SELECT a + b AS sum, c FROM table1, a + b + c AS my sorting_key ORDER BY
my_sorting_key; -- correct
Not quite correct, because you get an extra unnecessary output column.
You can either not use an alias in ORDER BY:
SELECT a + b AS sum, c FROM table1 ORDER BY a + b + c;
or you can use a subquery:
SELECT sum, c
FROM (SELECT a + b AS sum, c
FROM table1) AS subq
ORDER BY sum + c;
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com