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

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 2

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