Re: filtering out doubles

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.