Thread: filtering out doubles
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!
Distinct on is probably your answer. Something like select distinct on(col2) col1,col2,col3 from mytable The traditional disclamer that goes with this is that distinct on is not SQL standard and is therefore not portable. But it works very well HTH Chad ----- Original Message ----- From: "Jules Alberts" <jules.alberts@arbodienst-limburg.nl> To: <pgsql-sql@postgresql.org> Sent: Wednesday, March 12, 2003 3:03 AM Subject: [SQL] filtering out doubles > 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! > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
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.