Received: from mail.enterprise.net (root@mail.enterprise.net [194.72.192.18]) by hub.org (8.9.2/8.9.1) with ESMTP id JAA60416 for ; Thu, 28 Jan 1999 09:55:40 -0500 (EST) (envelope-from olly@lfix.co.uk) Received: from linda.lfix.co.uk (root@max06-085.enterprise.net [194.72.198.85]) by mail.enterprise.net (8.8.5/8.8.5) with ESMTP id OAA06139; Thu, 28 Jan 1999 14:55:35 GMT Received: from linda.lfix.co.uk (olly@localhost [127.0.0.1]) by linda.lfix.co.uk (8.9.2/8.9.1/Debian/GNU) with ESMTP id OAA23605; Thu, 28 Jan 1999 14:55:28 GMT Message-Id: <199901281455.OAA23605@linda.lfix.co.uk> X-Mailer: exmh version 2.0.2 2/24/98 (debian) To: Thomas Metz cc: pgsql-sql@hub.org Subject: Re: [SQL] SELECT DISTINCT ON ... ORDER BY ... In-Reply-To: Message from Thomas Metz of "Thu, 28 Jan 1999 14:01:03 +0100." <36B05F8F.DFB94ED8@gsf.de> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Date: Thu, 28 Jan 1999 14:55:27 +0000 From: "Oliver Elphick" Thomas Metz wrote: >I have the following problem: > >Assuming the table TEST as follows: > >ID NAME >----------------- >1 Alex >2 Oliver >1 Thomas >2 Fenella ... >SELECT DISTINCT ON id, name FROM test ORDER BY name; >produces: > >ID NAME >----------------- >1 Alex >2 Fenella >1 Thomas > >I would have expected only two rows in both queries. I don't care which >names actually appear in the output as long as they are sorted, but >there should no longer be duplicate id's. I looked at the documentation for SELECT and found that DISTINCT ON is mentioned but not explained; there are also some other parts of that documentation that need expanding or correcting, so I'm doing that at the moment. Is there any intended difference between DISTINCT ON and GROUP BY? (I realise from this thread that there is an actual difference!) Am I correct in saying that DISTINCT ON is not part of SQL92? -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID 32B8FAA1 ======================================== "Many are the afflictions of the righteous; but the LORD delivereth him out of them all." Psalm 34:19