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 = '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)

iD8DBQFF1OK9S9HxQb37XmcRAhC9AJ9YKyb2HRhr+FAaWQluMG86lyV6egCgu0LU
3KT/s+eq5KKHSYDnpRKuyu4=
=SgpW
-----END PGP SIGNATURE-----

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

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 <ron.l.johnson@cox.net> wrote:

-----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@cox.net> 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-----

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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] 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 <ron.l.johnson@cox.net> 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@postgresql.org so that your
message can get through to the mailing list cleanly

These seems to work, thx... :)

BTJ

On Fri, 16 Feb 2007 09:23:44 -0600
"Adam Rich" <adam.r@sbcglobal.net> wrote: