X-Original-To: pgsql-sql@postgresql.org Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id 007CF4758E6 for ; Wed, 12 Mar 2003 15:17:46 -0500 (EST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 71C24D60A; Wed, 12 Mar 2003 12:17:46 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 677DD5C04; Wed, 12 Mar 2003 12:17:46 -0800 (PST) Date: Wed, 12 Mar 2003 12:17:46 -0800 (PST) From: Stephan Szabo To: Jules Alberts Cc: Subject: Re: filtering out doubles In-Reply-To: <20030312100410.BBFD01CB1A5@koshin.dsl.systemec.nl> Message-ID: <20030312121542.F55615-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200303/307 X-Sequence-Number: 12346 On Wed, 12 Mar 2003, Jules Alberts wrote: > Hello everyone, > > I was wondering how to filter out double values (where "value" spans > two columns COL1 and COL2) with a SELECT statement. If I have this > table: > > COL1 |COL2 |COL3 > ----------------------------- > a |b |some value > a |b |another value > a |c |yet another value > > what would be the select statement to get this result: > > COL1 |COL2 |COL3 > ----------------------------- > a |b |some value > a |c |yet another value > > I was thinking that DISTINCT would do this, but I don't want to loose > COL3. TIA for any tips! If you're willing to use a postgresql extension, select distinct on (col1, col2) * ... That'll give you a pretty much arbitrarily chosen col3 for a col1,col2 pair that has duplicates. You can get some control over which one is chosen using order by IIRC.