Thread: 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