pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Philip Warner <pjw@rhyme.com.au>
To: Peter Eisentraut <peter_e@gmx.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@postgreSQL.org
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
Date: Fri, 28 Jan 2000 12:21:18 +1100
Message-ID: <3.0.5.32.20000128122118.009d2100@mail.rhyme.com.au> (raw)
In-Reply-To: <Pine.LNX.4.21.0001271922100.356-100000@localhost.localdoma in>
References: <18389.948912054@sss.pgh.pa.us>

At 23:28 27/01/00 +0100, Peter Eisentraut wrote:
>
>select one.a, two.b, two.c
>from
>    (select a, min(b) as "min_b" from test group by a) as one,
>    (select b, c from test) as two
>where one."min_b" = two.b
>
>Not sure if this is completely legal as it stands but at least the idea
>would be to join the grouped select with the plain one to get the c
>corresponding to the minimum b. But of course we don't offer that, so it's
>distinct on until then. (It would really surprise me if the distinct on
>functionality was not at all possible to emulate using SQL, since in my
>experience it is fairly complete with regards to querying options at
>least.)

You are quite right - with a complete SQL impleentation, DISTINCT ON
becomes superfluous. Although it may give the optimizer usefull hints as to
how to approach the query. You actually have to be a bit more carefull to
avoid duplicates, something like:

Select 
   a,b,c
From 
    (Select a, min(b) from test group by a) as one(a,min_b),
    (Select b, c from test t2 where t2.a = one.a limit to 1 row) as two

This is legal on the DB I use most of the time.






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



view thread (36+ messages)

Message-ID: <3.0.5.32.20000128122118.009d2100@mail.rhyme.com.au>
Permalink:  ../3.0.5.32.20000128122118.009d2100@mail.rhyme.com.au/
Also on:    postgresql.org/message-id/3.0.5.32.20000128122118.009d2100@mail.rhyme.com.au

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: pjw@rhyme.com.au, peter_e@gmx.net, tgl@sss.pgh.pa.us, pgsql-hackers@postgreSQL.org
  Subject: Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
  In-Reply-To: <3.0.5.32.20000128122118.009d2100@mail.rhyme.com.au>
  References: <18389.948912054@sss.pgh.pa.us> <3.0.5.32.20000128122118.009d2100@mail.rhyme.com.au>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox