Re: documentation extension request - order with function overaggregated functions - Mailing list pgsql-docs

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