By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,826 Members | 2,443 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,826 IT Pros & Developers. It's quick & easy.

SELECT duplicates in a table

P: n/a
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

Nov 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

Nov 23 '05 #2

P: n/a
Thanks. Worked like a charm!

Bruce
vh*****@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



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #3

P: n/a
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

Nov 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.