Find duplicate rows in database.

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

I 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, ...
;

Answer 6

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
)