0

Ive got a table like this, where I'm looking for unnecessary duplicate rows:

enter image description here

I want to find any rows where the First Name, Last Name, and Occupation columns are identical - in this case rows 1 and 3. I don't want to specify what the identical values should be as I dont know.

I've tried the answer to this question, but I dont think it applies to this case.

Community
  • 1
  • 1
blarg
  • 3,773
  • 11
  • 42
  • 71

2 Answers2

1

simple solution is to add a HAVING clause where there are duplicates after grouping by all three columns

SELECT 
    ID, FirstName, LastName, Occupation, Age
FROM table1
GROUP BY 
    FirstName, 
    LastName, 
    Occupation
HAVING COUNT(*) > 1

here is a DEMO with two duplicate rows to ensure it works properly

EDIT:

my first understanding was you wanted one row returned when it has duplicates.. if you want a query that will return all duplicate rows.. then here it is... this will return rows 1 and 3

SELECT p1.* FROM people p
JOIN people p1 
    ON p1.firstname = p.firstname 
    AND p1.lastname = p.lastname  
    AND p1.occupation = p.occupation
GROUP BY id
HAVING COUNT(*) > 1;

another DEMO

John Ruddell
  • 25,283
  • 6
  • 57
  • 86
  • Nice! I was trying to use some complicate `join`s, but your solution is much more elegant! – Cynical Jul 14 '14 at 14:58
  • @Cynical thanks :) i've tried writing so many complicated queries just to get bested by some simple solution too lol – John Ruddell Jul 14 '14 at 15:06
  • Your query returns rows 1 and 2, not rows 1 and 3. – blarg Jul 14 '14 at 15:12
  • @blarg it returns rows 1 and 2 because I added a duplicate for row 2. it groups rows 1 and 3 together and rows 2 and 4 together because those are duplicates.. look at the fiddle INSERT INTO TABLE part.. row 1 and 3 are duplicates and 2 and 4 are duplicates – John Ruddell Jul 14 '14 at 15:15
  • @blarg I just edited my answer with another query... this second query will return each duplicate row.. as in it will return rows 1 and 3.. you can test it with more rows if you would like.. what I thought you wanted was just to return one row that had duplicates – John Ruddell Jul 14 '14 at 15:37
0

Self join, 3 times (untested): SELECT a.* from your_table a, your_table b, your_table c, your_table d where a.fname = b.fname and a.lname=c.lname and a.occupation=d.occupation

user3741598
  • 297
  • 1
  • 12