Обсуждение: filtering out doubles with SELECT

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!

> 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

Sorry for answering myself, I already found it:

select * from tbl t1 where not exists (select * from tbl t2 where
t1.COL1=t2.COL1 and t1.COL2=t2.COL2 and t1.oid <> t2.oid)

On Wed, Mar 12, 2003 at 12:11:05 +0100,
  Jules Alberts <jules.alberts@arbodienst-limburg.nl> wrote:
>
> select * from tbl t1 where not exists (select * from tbl t2 where
> t1.COL1=t2.COL1 and t1.COL2=t2.COL2 and t1.oid <> t2.oid)

Using DISTINCT ON might be faster (though it's nonstandard).

> On Wed, Mar 12, 2003 at 12:11:05 +0100,
>   Jules Alberts <jules.alberts@arbodienst-limburg.nl> wrote:
> >
> > select * from tbl t1 where not exists (select * from tbl t2 where
> > t1.COL1=t2.COL1 and t1.COL2=t2.COL2 and t1.oid <> t2.oid)
>
> Using DISTINCT ON might be faster (though it's nonstandard).

Thanks, I didn't know that one!