pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feeddocumentation extension request - order with function over aggregated functions
2+ messages / 2 participants
[nested] [flat]
* documentation extension request - order with function over aggregated functions
@ 2020-02-04 08:59 PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Doc comments form @ 2020-02-04 08:59 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: estanislao.gonzalez@projo.zone
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
```
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: documentation extension request - order with function over aggregated functions
@ 2020-02-04 18:06 Laurenz Albe <laurenz.albe@cybertec.at>
parent: PG Doc comments form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: Laurenz Albe @ 2020-02-04 18:06 UTC (permalink / raw)
To: estanislao.gonzalez@projo.zone; pgsql-docs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2020-02-04 18:06 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-02-04 08:59 documentation extension request - order with function over aggregated functions PG Doc comments form <noreply@postgresql.org>
2020-02-04 18:06 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox