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
andalso 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