Обсуждение: documentation extension request - order with function over aggregated functions
documentation extension request - order with function over aggregated functions
От
PG Doc comments form
Дата:
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 ```
Re: documentation extension request - order with function overaggregated functions
От
Laurenz Albe
Дата:
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