From: | Edward Macnaghten <eddy(at)edlsystems(dot)com> |
---|---|
To: | "Kall, Bruce A(dot)" <kall(at)mayo(dot)edu> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: SELECT duplicates in a table |
Date: | 2004-11-22 18:14:19 |
Message-ID: | 41A22C7B.2080608@edlsystems.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Assuming identification_number is a unique (primary) key...
select * from my_table where date_of_birth in (select date_of_birth
from my_table group by date_of_birth having count(*) > 1)
Or - it may be quicker to do...
select * from my_table a where exists (select 'x' from my_table b where
a.date_of_birth = b.date_of_birth group by b.date_of_birth having
count(*) > 1)
Kall, Bruce A. wrote:
> I've look for a solution to this, but have only been able to find
> solutions to delete duplicate entries in a table by deleting entries
> not returned by SELECT DISTINCT.
>
> What sql should I use to SELECT entries in a table that have two
> particular column values that match?
>
> For example, my_table has
> name, phone number, identification_number, zip code, date of birth,
> and city
>
> I want to SELECT rows from this table that have the same values in
> identification and date of birth (duplicates) so I can have the user
> look at them in order to figure out which one to delete.
>
> I tried something like:
>
> $db_sql = "SELECT * FROM my_table GROUP BY identification_number
> HAVING count(date_of_birth) > 1 ORDER BY name"
>
> but that doesn't seem to work.
>
> Thanks,
> Bruce
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
From | Date | Subject | |
---|---|---|---|
Next Message | Gary L. Burnore | 2004-11-22 19:52:12 | Re: RFD: comp.databases.postgresql.* |
Previous Message | Kall, Bruce A. | 2004-11-22 18:07:55 | Re: SELECT duplicates in a table |