Thread: Problem writing sql statement....

Problem writing sql statement....

From

Bjørn T Johansen

Date:

15 February 2007, 21:16:14

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?


Regards,

BTJ

--
-----------------------------------------------------------------------------------------------
Bjørn T Johansen

btj@havleik.no
-----------------------------------------------------------------------------------------------
Someone wrote:
"I understand that if you play a Windows CD backwards you hear strange Satanic messages"
To which someone replied:
"It's even worse than that; play it forwards and it installs Windows"
-----------------------------------------------------------------------------------------------

Re: Problem writing sql statement....

From

Ron Johnson

Date:

15 February 2007, 21:46:37

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

Re: Problem writing sql statement....

From

"A. Kretschmer"

Date:

16 February 2007, 12:36:25

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

Re: Problem writing sql statement....

From

Bjørn T Johansen

Date:

16 February 2007, 13:32:23

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/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-----
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

Re: Problem writing sql statement....

From

Ron Johnson

Date:

16 February 2007, 14:13:40

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

Re: Problem writing sql statement....

From

"Adam Rich"

Date:

16 February 2007, 14:22:14

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

Re: Problem writing sql statement....

From

Bjørn T Johansen

Date:

17 February 2007, 09:10:52

These seems to work, thx... :)

BTJ

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