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

At 14:09 25/01/00 +1100, Chris Bitmead wrote:
>Philip Warner wrote:
>> >I don't feel overly strongly about this, but if I remember right you can
>> >do some pretty cool things with this feature, provided you do define
>> >some semantics clearly. Like I think you can find the first tuple
>> >(given some ORDER BY clause) that fulfills some criteria. I think it is
>> >
>> >SELECT DISTINCT ON name name, age ORDER BY age;
>> >
>> >will get the youngest person. This might not be clearly specified now,
>> >but
>> >as long as it's useful, how about clearly defining it? I don't know that
>> >there is an easy way of doing this in standard SQL.
>> 
>> I don't know about PGSQL, but in other systems, I use:
>> 
>>     Select <whatever> from <wherever> order by age asc limit to 1 row;
>> 
>> I *think* the PGSQL syntax is:
>> 
>>     Select <whatever> from <wherever> order by age asc limit 1;
>
>I think what I really meant was...
>
>SELECT DISTINCT ON firstname firstname, age ORDER BY age.
>
>Which would find the youngest person called "fred", the youngest person
>called "paul", the youngest person called "jim" etc etc. which your
>limit example wouldn't do.

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.

eg.
   SELECT firstname, FIRST(age) ORDER BY age.

Just an idea, but it seems like a cute solution for the more general problem.


----------------------------------------------------------------
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   |/