Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace

At 21:20 24/01/00 -0800, Don Baccus wrote:
>At 12:01 AM 1/25/00 -0500, Tom Lane wrote:
>>Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
>>> Just a thought, but would I be right in saying that this could be easily
>>> done with the addition of a new aggregate function 'FIRST', which simply
>>> returns the first value sent to it? Since the aggregates operate a row at
>>> a time, you are guaranteed a consistent set of values, I think.
>>
>>No, because the system doesn't guarantee to deliver tuples to the
>>aggregate in any particular order.
>
>In fact, this is a KEY notion behind RDBMS systems...queries return an
>unordered set, pure and simple, unless you supply an "order" clause
>(though "group by" appears to be implemented by ordering in various
>RDBMS systems, that's an efficiency hack not a given AFAIK).

That's why there was an 'order by' in the query (there should have also
been a 'group by', to make it valid SQL). I had (naievely) assumed that the
aggregates would be passed the rows in sorted order.

>>SQL in general doesn't believe that tuple ordering has any semantic
>>significance --- you can ask for ORDER BY, but that's only honored
>>at the end stage of SELECT result delivery, not necessarily anywhere
>>in the bowels of a query.
>
>Yep...
>

Out of curiosity, does the SQL spec give any rules or guidelines about when
aggregates should be applied to resultant rows? Or is it one of the
implementation-dependant things?

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: +61-03-5367 7422 | _________ \
Fax: +61-03-5367 7430 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/