These seems to work, thx... :) BTJ On Fri, 16 Feb 2007 09:23:44 -0600 "Adam Rich" <adam.r@xxxxxxxxxxxxx> wrote: > > 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 > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster
