am Thu, dem 15.02.2007, um 22:13:31 +0100 mailte Bjørn T Johansen folgendes: > I have a table that I want to find rows that have the same value in two fields, e.g. all rows that have the same date and also the > same productionid... > How do I write such an sql statement? I'm not sure if I understand your problem, but I hope. test=*# select * from foo; id | a | b ----+---+--- 1 | 1 | 1 2 | 1 | 2 3 | 2 | 2 4 | 2 | 3 5 | 2 | 2 (5 rows) You want to see duplacte records on (a,b), right? In this case id 3 and 5, both have (2,2). test=*# select distinct a, b, count(1) from foo group by a,b having count(1) > 1; a | b | count ---+---+------- 2 | 2 | 2 (1 row) If you want to know the id-column: test=*# select * from foo where (a,b) in (select distinct a, b from foo group by a,b having count(1) > 1); id | a | b ----+---+--- 3 | 2 | 2 5 | 2 | 2 (2 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
