Greg Stark <gsstark@mit.edu> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> Oh? How is a first() aggregate going to know what sort order you want
>> within the group?
> It would look something like
> select x,first(a),first(b) from (select x,a,b from table order by x,y) group by x
> which is equivalent to
> select DISTINCT ON (x) x,a,b from table ORDER BY x,y
No, it is not. The GROUP BY has no commitment to preserve order ---
consider for example the possibility that we implement the GROUP BY by
hashing.
> The group by can see that the subquery is already sorted by x and
> doesn't need to be resorted. In fact I believe you added the smarts to
> detect that condition in response to a user asking about precisely
> this type of scenario.
The fact that an optimization is present does not make it part of the
guaranteed semantics of the language.
Basically, first() is a broken concept in SQL. Of course DISTINCT ON
is broken too for the same reasons, but I do not see that first() is
one whit less of a kluge than DISTINCT ON.
regards, tom lane
