Message-ID: <5947ef175e787a4b298dfcf7879698f799fcfab7.camel@cybertec.at>
Subject: Re: documentation extension request - order with function over
 aggregated functions
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: estanislao.gonzalez@projo.zone, pgsql-docs@lists.postgresql.org
Date: Tue, 04 Feb 2020 19:06:44 +0100
In-Reply-To: <158080678185.1098.6542165890616974586@wrigleys.postgresql.org>
References: <158080678185.1098.6542165890616974586@wrigleys.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.32.5 (3.32.5-1.fc30) 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

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