pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSelecting exactly one row for each column value
5+ messages / 4 participants
[nested] [flat]
* Selecting exactly one row for each column value
@ 2007-03-06 15:03 Florian Weimer <fweimer@bfk.de>
0 siblings, 3 replies; 5+ messages in thread
From: Florian Weimer @ 2007-03-06 15:03 UTC (permalink / raw)
To: pgsql-sql
I've got the following table:
fweimer=> SELECT * FROM tab;
a | b | c
---+---+---
1 | 2 | 3
5 | 6 | 7
1 | 2 | 2
2 | 3 | 4
1 | 2 | 2
2 | 3 | 4
For each value in the first column, I need one (and only one) matching
row from the table. A possible solution is:
a | b | c
---+---+---
5 | 6 | 7
2 | 3 | 4
1 | 2 | 3
Of course,
SELECT a, (SELECT b FROM tab i WHERE i.a = o.a LIMIT 1),
(SELECT c FROM TAB i WHERE i.a = o.a LIMIT 1) FROM tab o GROUP BY o.a;
does the trick, but this approach seems to rely on undefined behavior
and quickly gets messy when the number of columns increases.
Is there a better way to implement this?
--
Florian Weimer <fweimer@bfk.de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Selecting exactly one row for each column value
@ 2007-03-06 15:17 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Florian Weimer <fweimer@bfk.de>
2 siblings, 0 replies; 5+ messages in thread
From: A. Kretschmer @ 2007-03-06 15:17 UTC (permalink / raw)
To: pgsql-sql
am Tue, dem 06.03.2007, um 16:03:36 +0100 mailte Florian Weimer folgendes:
> Is there a better way to implement this?
DISTINCT ON()
http://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Selecting exactly one row for each column value
@ 2007-03-06 15:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: Florian Weimer <fweimer@bfk.de>
2 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2007-03-06 15:32 UTC (permalink / raw)
To: Florian Weimer <fweimer@bfk.de>; +Cc: pgsql-sql
Florian Weimer <fweimer@bfk.de> writes:
> For each value in the first column, I need one (and only one) matching
> row from the table. A possible solution is:
SELECT DISTINCT ON would do it, if you don't mind a non-portable solution.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Selecting exactly one row for each column value
@ 2007-03-06 15:46 Florian Weimer <fweimer@bfk.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Florian Weimer @ 2007-03-06 15:46 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
* Tom Lane:
> Florian Weimer <fweimer@bfk.de> writes:
>> For each value in the first column, I need one (and only one) matching
>> row from the table. A possible solution is:
>
> SELECT DISTINCT ON would do it, if you don't mind a non-portable solution.
Cool, thanks a lot.
--
Florian Weimer <fweimer@bfk.de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Selecting exactly one row for each column value
@ 2007-03-06 18:14 Stefan Becker <pgsql@yukonho.de>
parent: Florian Weimer <fweimer@bfk.de>
2 siblings, 0 replies; 5+ messages in thread
From: Stefan Becker @ 2007-03-06 18:14 UTC (permalink / raw)
To: pgsql-sql
Am Dienstag, 6. März 2007 16:03 schrieb Florian Weimer:
> a | b | c
> ---+---+---
> 5 | 6 | 7
> 2 | 3 | 4
> 1 | 2 | 3
Hi,
couldn't you accomplish this by:
select distinct on (a) * from tablename order by a;
here:
create table tab (a int,b int,c int);
insert into tab values (1,2,3);
insert into tab values (5,6,7);
insert into tab values (1,2,3);
insert into tab values (2,3,4);
insert into tab values (1,2,2);
insert into tab values (2,3,4);
select * from tab;
a | b | c
---+---+---
1 | 2 | 3
5 | 6 | 7
1 | 2 | 3
2 | 3 | 4
1 | 2 | 2
2 | 3 | 4
(6 rows)
select distinct on (a) * from tab order by a;
a | b | c
---+---+---
1 | 2 | 3
2 | 3 | 4
5 | 6 | 7
(3 rows)
my regards,
Stefan
--
email: stefan@yukonho.de
tel : +49 (0)6232-497631
http://www.yukonho.de
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-03-06 18:14 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-06 15:03 Selecting exactly one row for each column value Florian Weimer <fweimer@bfk.de>
2007-03-06 15:17 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-03-06 15:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-03-06 15:46 ` Florian Weimer <fweimer@bfk.de>
2007-03-06 18:14 ` Stefan Becker <pgsql@yukonho.de>
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