From: Laurenz Albe <laurenz.albe@cybertec.at>
To: estanislao.gonzalez@projo.zone
To: pgsql-docs@lists.postgresql.org
Subject: Re: documentation extension request - order with function over aggregated functions
Date: Tue, 04 Feb 2020 19:06:44 +0100
Message-ID: <5947ef175e787a4b298dfcf7879698f799fcfab7.camel@cybertec.at> (raw)
In-Reply-To: <158080678185.1098.6542165890616974586@wrigleys.postgresql.org>
References: <158080678185.1098.6542165890616974586@wrigleys.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: laurenz.albe@cybertec.at, estanislao.gonzalez@projo.zone, pgsql-docs@lists.postgresql.org
Subject: Re: documentation extension request - order with function over aggregated functions
In-Reply-To: <5947ef175e787a4b298dfcf7879698f799fcfab7.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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