Re: documentation extension request - order with function over aggregated functions

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