Or, if you need the whole row: SELECT at1.* FROM a_table as at1 WHERE EXISTS ( SELECT 1 FROM a_table as at2 WHERE at2.my_date = at1.my_date AND at2.prod_id = at1.prod_id AND at2.primary_key <> at1.primary_key ) This form can easily be adjusted to show only certain duplicates, or only to delete certain duplicates. -----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Ron Johnson Sent: Friday, February 16, 2007 9:13 AM To: pgsql-general@xxxxxxxxxxxxxx Subject: Re: [GENERAL] Problem writing sql statement.... -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 02/16/07 01:44, Bjørn T Johansen wrote: > Not exactly what I want... I don't know the date or id, I just > need to find all rows that have the same date and the same id.. SELECT SOME_DATE, PRODUCTIONID, COUNT(*) FROM A_TABLE GROUP BY SOME_DATE, PRODUCTIONID HAVING COUNT(*) > 1; > > BTJ > > On Thu, 15 Feb 2007 16:46:21 -0600 > Ron Johnson <ron.l.johnson@xxxxxxx> wrote: > > On 02/15/07 15:13, Bjørn T Johansen wrote: >>>> 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? > If I understand your question: > > SELECT FIELD_1, FIELD_2, COUNT(*) > FROM A_TABLE > WHERE SOME_DATE = 'yyyy-mm-dd' > AND PRODUCTIONID = nnnn > GROUP BY FIELD_1, FIELD_2 > HAVING COUNT(*) > 1; -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF1coSS9HxQb37XmcRAlj5AJ94KSt0BCWwFehMNha4Ljf/Cr0tDQCg6AZN JF4XWsS68ru0jsNaQjvHo20= =AKKx -----END PGP SIGNATURE----- ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
