I was hoping the proposal would be judged on its merits - primarily usefulness, not necessarily conforming to the standards I would expect postgres overlap with the SQL standard (eg. SQL:2016) doesn't differ much from other big vendors and has lots of own deviations/extensions itself. Not a fan of Oracle myself but have to give it to them the mentioned versatility is really nice. In terms of performance it also carries a significant difference: being forced to work on an unaggregated result and process it further (eg. with extra subselect filtering on row_number) vs. simple GROUP BY
Yes it would require particular ordering of the input but please note PostgreSQL already supports a small set of such Ordered-Set Aggregate Functions <https://www.postgresql.org/docs/devel/static/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE> ( https://www.postgresql.org/docs/devel/static/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE ) Currently I can do workaround with something like: SELECT gid ,RIGHT(MIN(lpad(v2::varchar,10,'0')||v1),-10) pseudo_lkp_first ,RIGHT(MAX(lpad(v2::varchar,10,'0')||v1),-10) pseudo_lkp_last FROM( VALUES (1, 'b', 3),(1, 'd', 1),(1, 'a', 2) ,(2, 'x', 7),(2, 'y', 9),(2, 'z', 8),(2, 'v', 9)) t (gid, v1, v2) GROUP BY gid; but it's neither universal nor readable. It's fast though. I was hoping those FIRST/LAST lookup functions could be made available as some new Ordered-Set Aggregate Functions <https://www.postgresql.org/docs/devel/static/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE> too… On Tue, 24 Jul 2018 at 23:16, Tom Lane <t...@sss.pgh.pa.us> wrote: > matshyeq <matsh...@gmail.com> writes: > > I'd like to throw here an enhancement proposal to discuss/consider. > > The FIRST/LAST_value() > > functions offer powerful lookup capabilities, ... > > but, given those values are repeating - why can't I simply use this > > functions as regular aggregates? > > Or can I? It doesn't seem to be possible while I find this use case > > actually more common than in windowing context… > > The reason the SQL standard doesn't allow that usage, or similar ones > for other window functions, is that the function results are undefined > unless you specify a particular ordering of the inputs ... which is > exactly what the window syntax provides. > > Yeah, in most cases there are other ways where you could get the same > result, but all of them require extra syntax too. So you might as > well use window syntax and be standards-conforming. > > > Anyway, Oracle is an example where both contexts are possible > > Oracle is not exactly a shining example of either SQL spec compliance > or well-thought-out features ... > > regards, tom lane >
