pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfiltering out doubles
3+ messages / 3 participants
[nested] [flat]
* filtering out doubles
@ 2003-03-12 10:03 Jules Alberts <jules.alberts@arbodienst-limburg.nl>
0 siblings, 2 replies; 3+ messages in thread
From: Jules Alberts @ 2003-03-12 10:03 UTC (permalink / raw)
To: pgsql-sql
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!
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: filtering out doubles
@ 2003-03-12 20:03 Chad Thompson <chad@weblinkservices.com>
parent: Jules Alberts <jules.alberts@arbodienst-limburg.nl>
1 sibling, 0 replies; 3+ messages in thread
From: Chad Thompson @ 2003-03-12 20:03 UTC (permalink / raw)
To: Jules Alberts <jules.alberts@arbodienst-limburg.nl>; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: filtering out doubles
@ 2003-03-12 20:17 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Jules Alberts <jules.alberts@arbodienst-limburg.nl>
1 sibling, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2003-03-12 20:17 UTC (permalink / raw)
To: Jules Alberts <jules.alberts@arbodienst-limburg.nl>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-03-12 20:17 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-12 10:03 filtering out doubles Jules Alberts <jules.alberts@arbodienst-limburg.nl>
2003-03-12 20:03 ` Chad Thompson <chad@weblinkservices.com>
2003-03-12 20:17 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
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