Received: from acheron.rime.com.au (root@albatr.lnk.telstra.net [139.130.54.222]) by hub.org (8.9.3/8.9.3) with ESMTP id UAA48328; Thu, 27 Jan 2000 20:21:25 -0500 (EST) (envelope-from pjw@rhyme.com.au) Received: from oberon (Oberon.rime.com.au [203.8.195.100]) by acheron.rime.com.au (8.9.3/8.9.3) with SMTP id MAA14668; Fri, 28 Jan 2000 12:19:44 +1100 Message-Id: <3.0.5.32.20000128122118.009d2100@mail.rhyme.com.au> X-Sender: pjw@mail.rhyme.com.au X-Mailer: QUALCOMM Windows Eudora Pro Version 3.0.5 (32) Date: Fri, 28 Jan 2000 12:21:18 +1100 To: Peter Eisentraut , Tom Lane From: Philip Warner Subject: Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Cc: pgsql-hackers@postgreSQL.org, pgsql-sql@postgreSQL.org In-Reply-To: References: <18389.948912054@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" 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 |/