Обсуждение: 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!