Thread: SELECT duplicates in a table

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

Try

SELECT *
  FROM mytable
 WHERE (identification_number,date_of_birth) IN
          (SELECT identification_number
                , date_of_birth
             FROM mytable m2
            GROUP BY identification_number,data_of_birth
           HAVING COUNT(*) > 1
          )

There are other ways of doing it, perhaps more efficient.

Vincent

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

Thanks.  Worked like a charm!

Bruce


vhikida@inreach.com wrote:
> Try
>
> SELECT *
>   FROM mytable
>  WHERE (identification_number,date_of_birth) IN
>           (SELECT identification_number
>                 , date_of_birth
>              FROM mytable m2
>             GROUP BY identification_number,data_of_birth
>            HAVING COUNT(*) > 1
>           )
>
> There are other ways of doing it, perhaps more efficient.
>
> Vincent
>
>
>>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
>>
>
>
>

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
>