How do find duplicate rows? If last_name is the duplicate field, I want to have to display
last_name frst_name frst_name1 frst_name2 .... Any database will do, prefer oracle.
- Which database server are you using?Matti Virkkunen– Matti Virkkunen2010-09-22 21:23:25 +00:00Commented Sep 22, 2010 at 21:23
- Each database has its own recipe, but these are database specific.Peter Tillemans– Peter Tillemans2010-09-22 21:25:17 +00:00Commented Sep 22, 2010 at 21:25
- I mean different tricks to get one copy, but details as row_id, row_num differ. But I misinterpreted the OP's question.Peter Tillemans– Peter Tillemans2010-09-22 21:53:48 +00:00Commented Sep 22, 2010 at 21:53
Add a comment |
7 Answers
This should work on pretty much every SQL dialect:
SELECT last_name, first_name FROM names WHERE last_name IN ( SELECT last_name FROM names GROUP BY last_name HAVING COUNT(*) > 1 ) It will give you a result set like this, though:
Smith Jack Smith Joe Smith Anna Sixpack Joe Sixpack Eve The most elegant solution for displaying this in the desired format IMO is to just re-arrange the result set programmatically in the client application instead of pulling all sorts of obscure SQL stunts; something like (pseudocode):
for each row in resultset if row[last_name] <> previous_last_name print newline, print last_name print ' ' print first_name 
Comments
Assuming your server has GROUP_CONCAT because you didn't mention which one you're using:
SELECT GROUP_CONCAT(first_name SEPARATOR ' ') FROM table GROUP BY last_name HAVING COUNT(first_name) > 1 
2 Comments
eksortso
I like this a lot! For now, though,
GROUP_CONCAT is only implemented in MySQL. There's an Oracle version at the following link, and the same blog has versions for PostGreSQL and SQL Server, too. explainextended.com/2009/04/05/group_concat-in-oracle-10g
OMG Ponies
@eksortso: SQLite also supports the GROUP_CONCAT function.
Ha, lots of queries. Here is more
SELECT last_name, first_name FROM names n1 WHERE ( SELECT count(*) FROM names n2 where n2.last_name = n1.last_name ) > 1 or if table has unique identifier
SELECT last_name, first_name FROM names n1 WHERE exists ( SELECT id FROM names n2 where n2.last_name = n1.last_name and n1.id <> n2.id ) 
Comments
Select a.* from persons a inner join persons b on (a.personID<>b.PersonID and a.last_name=b.last_name) PersonID is your table's primary key.
Comments
I do not know if this is what you are asking for, but I think what you are looking for is SELECT * FROM users u1, users u2 WHERE (u1.last_name = u2.last_name AND COUNT(u1.last_name) > 1))
3 Comments
eksortso
That would get you a lot of duplicates if you have a very popular last name.
dirbacke
I've just answered the question... If you would like to check the other fields as well, then you'll just have to add them in the WHERE clause with the DISTINCT option.
OMG Ponies
Can't use an aggregate (IE: COUNT) in the WHERE clause, outside of a subquery. After you fixed that error,
SELECT * would return rows from both copies of the USERS tableI tried to devise a solution that would work in most ANSI-compliant SQL database servers. Here's what I came up with.
The idea here is that you identify the duplicated last_names, then pull all the records that have one of those duplicates.
SELECT t.last_name, t.frst_name, t.frst_name1, t.frst_name2, ... FROM our_table AS t WHERE t.last_name IN ( SELECT t0.last_name FROM our_table AS t0 GROUP BY t0.last_name HAVING COUNT(*) > 1 ) ORDER BY t.last_name, t.frst_name, t.frst_name1, t.frst_name2, ... ; Comments
Suppose "In table customer you have customerkey as PK" then you can use:
select customerkey,count(customerkey) from customer group by customerkey having count(customerkey)>1; This will give you all the duplicate customerkeys. Now you can delete them.
