Search Postgresql Archives

Re: Problem writing sql statement....

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux