Thread: 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