[GENERAL] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] SELECT duplicates in a table
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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] SELECT duplicates in a table
Thanks. Worked like a charm! Bruce [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] SELECT duplicates in a table
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
