python-sql

Bug 65

classification
Title: Add FIRST, LAST aggregate functions and ORDER BY clause
Type: rfe Severity: normal
Components: Versions:
process
Status: closed Resolution: wont fix
Dependencies: Superseder:
Assigned To: Nosy List: bell, ced, pokoli
Priority: normal Keywords:

Created on 2020-01-03 16:48 by bell, last changed 2020-01-18 00:20 by ced.

Files
File name Uploaded Description Edit Remove
unnamed bell, 2020-01-03 17:17
Messages
msg90 (view) Author: [hidden] (bell) Date: 2020-01-03 16:48
FIRST and LAST aggregate functions are often required.
Usually these functions need ORDER BY clause.

Here is a portable PostgreSQL implementation:
https://wiki.postgresql.org/wiki/First/last_(aggregate)
msg91 (view) Author: [hidden] (pokoli) Date: 2020-01-03 16:58
Is this part of the SQL standard or at list supported as is by any postgresql version?

Could you provide some links to the usage reference?
msg92 (view) Author: [hidden] (bell) Date: 2020-01-03 17:08
For me, this is not part of the SQL standard, but is supported by any version of PostgreSQL.
msg93 (view) Author: [hidden] (ced) Date: 2020-01-03 17:09
As far as I can see those aggregate functions are not standard. We should not implement non-standard functions especially if there is no common definition.
msg94 (view) Author: [hidden] (ced) Date: 2020-01-03 17:11
> but is supported by any version of PostgreSQL.

For me, it is not supported by PostgreSQL:

trunk=# select last(id) from party_party;
ERROR:  function last(integer) does not exist
LINE 1: select last(id) from party_party;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
msg95 (view) Author: [hidden] (bell) Date: 2020-01-03 17:17
First you must create these aggregate functions:
https://wiki.postgresql.org/wiki/First/last_(aggregate)

пт, 3 янв. 2020 г. в 18:11, Cédric Krier <python-sql@tryton.org>:

>
> Cédric Krier <cedric.krier@b2ck.com> added the comment:
>
> > but is supported by any version of PostgreSQL.
>
> For me, it is not supported by PostgreSQL:
>
> trunk=# select last(id) from party_party;
> ERROR:  function last(integer) does not exist
> LINE 1: select last(id) from party_party;
>                ^
> HINT:  No function matches the given name and argument types. You might
> need to add explicit type casts.
>
> __________________________________________
> python-sql tracker <python-sql@tryton.org>
> <https://python-sql.tryton.org/bug65>
> __________________________________________
>
msg96 (view) Author: [hidden] (ced) Date: 2020-01-03 17:30
> First you must create these aggregate functions

So it is clearly not standard. We can no assume everybody will create aggregate with this names with this definition.

For me, we must reject this request.
msg97 (view) Author: [hidden] (bell) Date: 2020-01-03 17:40
I disagree, these are commonly used aggregate functions.
For example, the FIRST and LAST functions in Oracle SQL:
http://sql.standout-dev.com/2018/09/the-first-and-last-functions-in-oracle-sql/
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions056.htm
msg98 (view) Author: [hidden] (ced) Date: 2020-01-03 17:56
On 2020-01-03 17:40, Victor wrote:
> I disagree, these are commonly used aggregate functions.
> For example, the FIRST and LAST functions in Oracle SQL:
> http://sql.standout-dev.com/2018/09/the-first-and-last-functions-in-oracle-sql/
> https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions056.htm

Well Oracle is not really known for following standard.
https://stackoverflow.com/a/46805009/687601 explains clearly why
FIRST/LAST aggregate can not be a standard function:
"because aggregation takes place before sorting".
The link also provides standard alternative (the missing statistics
aggregate function could be added).
msg99 (view) Author: [hidden] (bell) Date: 2020-01-03 18:22
Of course this doesn't make sense:
SELECT FIRST(field1) AS First,
       LAST(field1) AS Last
But this makes perfect sense:
SELECT FIRST(field1 ORDER BY field2) AS First,
       LAST(field1 ORDER BY field2) AS Last
msg100 (view) Author: [hidden] (ced) Date: 2020-01-03 22:07
I mark it as wont fix because it is not standard SQL.
msg101 (view) Author: [hidden] (bell) Date: 2020-01-04 11:42
It's a pity. These are pretty useful features.
History
Date User Action Args
2020-01-18 00:20:04cedsetstatus: new -> closed
2020-01-04 11:42:31bellsetmessages: + msg101
2020-01-03 22:07:21cedsetresolution: wont fix
messages: + msg100
2020-01-03 18:22:01bellsetmessages: + msg99
2020-01-03 17:56:08cedsetmessages: + msg98
2020-01-03 17:40:56bellsetmessages: + msg97
2020-01-03 17:30:58cedsetmessages: + msg96
2020-01-03 17:17:20bellsetfiles: + unnamed
messages: + msg95
2020-01-03 17:11:10cedsetmessages: + msg94
2020-01-03 17:09:46cedsetnosy: + ced
messages: + msg93
title: Add FIRST/LAST aggregate functions and ORDER BY clause -> Add FIRST, LAST aggregate functions and ORDER BY clause
2020-01-03 17:08:44bellsetmessages: + msg92
title: Add FIRST, LAST aggregate functions and ORDER BY clause -> Add FIRST/LAST aggregate functions and ORDER BY clause
2020-01-03 16:58:34pokolisetnosy: + pokoli
messages: + msg91
2020-01-03 16:48:13bellcreate