Обсуждение: Selecting exactly one row for each column value

I've got the following table:

fweimer=> SELECT * FROM tab;a | b | c
---+---+---1 | 2 | 35 | 6 | 71 | 2 | 22 | 3 | 41 | 2 | 22 | 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 | 72 | 3 | 41 | 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
oGROUP 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

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

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

* 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

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 | 35 | 6 | 71 | 2 | 32 | 3 | 41 | 2 | 22 | 3 | 4
(6 rows)

select distinct on (a) * from tab order by a;a | b | c
---+---+---1 | 2 | 32 | 3 | 45 | 6 | 7
(3 rows)



my regards,


Stefan






--
email: stefan@yukonho.de
tel  : +49 (0)6232-497631
http://www.yukonho.de