Обсуждение: 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 >