From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Jules Alberts <jules.alberts@arbodienst-limburg.nl>
Cc: pgsql-sql@postgresql.org
Subject: Re: filtering out doubles
Date: Wed, 12 Mar 2003 12:17:46 -0800 (PST)
Message-ID: <20030312121542.F55615-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <20030312100410.BBFD01CB1A5@koshin.dsl.systemec.nl>
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.
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone23.bigpanda.com, jules.alberts@arbodienst-limburg.nl
Subject: Re: filtering out doubles
In-Reply-To: <20030312121542.F55615-100000@megazone23.bigpanda.com>
* 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