Re: [GENERAL] Problem writing sql statement....
These seems to work, thx... :) BTJ On Fri, 16 Feb 2007 09:23:44 -0600 "Adam Rich" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Ron Johnson > Sent: Friday, February 16, 2007 9:13 AM > To: pgsql-general@postgresql.org > 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 <[EMAIL PROTECTED]> 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 = '-mm-dd' > > AND PRODUCTIONID = > > 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 [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly > > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Problem writing sql statement....
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ron Johnson Sent: Friday, February 16, 2007 9:13 AM To: pgsql-general@postgresql.org 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 <[EMAIL PROTECTED]> 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 = '-mm-dd' > AND PRODUCTIONID = > 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
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 <[EMAIL PROTECTED]> 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 = '-mm-dd' > AND PRODUCTIONID = > 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Problem writing sql statement....
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.. BTJ On Thu, 15 Feb 2007 16:46:21 -0600 Ron Johnson <[EMAIL PROTECTED]> wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > 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 = '-mm-dd' > AND PRODUCTIONID = > GROUP BY FIELD_1, FIELD_2 > HAVING COUNT(*) > 1; > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.6 (GNU/Linux) > > iD8DBQFF1OK9S9HxQb37XmcRAhC9AJ9YKyb2HRhr+FAaWQluMG86lyV6egCgu0LU > 3KT/s+eq5KKHSYDnpRKuyu4= > =SgpW > -END PGP SIGNATURE- > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Problem writing sql statement....
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Problem writing sql statement....
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 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 = '-mm-dd' AND PRODUCTIONID = GROUP BY FIELD_1, FIELD_2 HAVING COUNT(*) > 1; -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF1OK9S9HxQb37XmcRAhC9AJ9YKyb2HRhr+FAaWQluMG86lyV6egCgu0LU 3KT/s+eq5KKHSYDnpRKuyu4= =SgpW -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
