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
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.
matshyeq <matshyeq@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
