Received: from mailo.vtcif.telstra.com.au (mailo.vtcif.telstra.com.au [202.12.144.17]) by hub.org (8.9.3/8.9.3) with ESMTP id RAA23629; Wed, 26 Jan 2000 17:56:42 -0500 (EST) (envelope-from chris@bitmead.com) Received: (from uucp@localhost) by mailo.vtcif.telstra.com.au (8.8.2/8.6.9) id JAA19611; Thu, 27 Jan 2000 09:56:03 +1100 (EST) Received: from maili.vtcif.telstra.com.au(202.12.142.17) via SMTP by mailo.vtcif.telstra.com.au, id smtpd0Ekozu; Thu Jan 27 09:55:24 2000 Received: (from uucp@localhost) by maili.vtcif.telstra.com.au (8.8.2/8.6.9) id JAA19452; Thu, 27 Jan 2000 09:55:22 +1100 (EST) Received: from localhost(127.0.0.1), claiming to be "mail.cdn.telstra.com.au" via SMTP by localhost, id smtpdihYQm_; Thu Jan 27 09:54:53 2000 Received: from lunitari.nimrod.itg.telecom.com.au (lunitari.nimrod.itg.telecom.com.au [192.53.254.48]) by mail.cdn.telstra.com.au (8.8.2/8.6.9) with ESMTP id JAA09362; Thu, 27 Jan 2000 09:54:53 +1100 (EST) Received: from majere.design (majere [192.53.254.45]) by lunitari.nimrod.itg.telecom.com.au (8.9.1/8.9.3) with ESMTP id JAA22496; Thu, 27 Jan 2000 09:54:12 +1100 (EST) Received: from bitmead.com (localhost [127.0.0.1]) by majere.design (8.8.8+Sun/8.8.8) with ESMTP id JAA12754; Thu, 27 Jan 2000 09:53:29 +1100 (EST) Sender: chrisb@nimrod.itg.telstra.com.au Message-ID: <388F7AE9.EDD10859@bitmead.com> Date: Thu, 27 Jan 2000 09:53:29 +1100 From: Chris Bitmead Organization: IBM Global Services X-Mailer: Mozilla 4.6 [en] (X11; I; SunOS 5.6 sun4u) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-hackers@postgreSQL.org, pgsql-sql CC: Tom Lane Subject: Re: [HACKERS] Re: [SQL] DISTINCT ON: speak now or forever hold your peace References: <13995.948820606@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Tom Lane wrote: > SELECT DISTINCT ON (expression) target1, ... > One immediate advantage of allowing an expression is that you can do > distinct-on-two-fields in a rather klugy way, eg > SELECT DISTINCT ON (field1 || ' ' || field2) ... As long as we're fixing the syntax, I'm wondering if it wouldn't be more logical to have DISTINCT ON somewhere later in the syntax. I'm wondering if that might also avoid the need for () as a side effect. Like this perhaps.... SELECT x, y, z FROM foo WHERE z DISTINCT ON x, y ORDER BY x, y; > What I'd prefer to do is put in an > error check that says "if you use both DISTINCT ON and ORDER BY, then > the DISTINCT ON expression must be the first ORDER BY item". Better, but still a little kludgy. What about a syntax that really supports everything you want? Like maybe... SELECT x, y, z FROM foo DISTINCT ON x, y DESC ORDER BY z ASC; Distinct on now has a similar syntax to the order by clause. What this means is, do the DISTINCT ON test by ordering DESC (so you get the distinct item with the largest value of x, y), and then order the final result by z ascending. Unless I'm missing something that gives everybody what they want. HANG ON, I've got a better idea..... The other alternative is to make DISTINCT ON a bit like GROUP BY. So you would have something like SELECT x, y, max(z) AS mmm FROM foo DISTINCT ON x, y ORDER BY mmm; Like GROUP BY where you group by the non-aggregate fields in the SELECT clause, you use DISTINCT ON, to mention the non-aggregate clauses in the SELECT. The aggregate clause are used to select WHICH of the resulting fields are selected for presentation. This would have the benefit of being more general so you could select, say the city with the longest name in each state... SELECT state, zipcode, longest_string(cityname) FROM cities DISTINCT ON state ORDER BY zipcode; I don't know how far I'm stepping here from the SQL paradigm, but it sure seems cool.... Chris.