How to find duplicate rows based on multiple fields in MySQL? [duplicate]

Question

Possible Duplicate:
Mysql Duplicate Rows ( Duplicate detected using 2 columns )

In a MySQL database I have many rows. For example:

id | title   | time  | domain
32   title1    12:30   domain1.com
33   title1    12:30   domain2.com
34   title2    14:20   domain1.com
35   title3    14:30   domain2.com
36   title1    12:30   domain55.com

How am I able to select rows from a database based on only title and time? Duplicate domains or ID's are not of concern, only the other two fields.

I want to be able to retrieve rows 32, 33 and 36 because they have identical titles and identical times.

I don't want to have to put in a title or time, I want the query to return all fields where there's a "duplicate" match found on these two fields whether that be only two or 50. That way I can go through and edit or delete some of the duplicates.

kasi · Accepted Answer · 2012-10-02 13:53:07Z

66

Here is what you want

SELECT title, time  
  FROM table
GROUP BY title, time
  HAVING count(*) > 1

answered Oct 2, 2012 at 13:53

kasi

7744 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vladimir Ch Over a year ago

it will skip next rows having title="title1" with time="10:00" cause grouping by the order of operation is SQL: FROM clause WHERE clause GROUP BY clause HAVING clause SELECT clause ORDER BY claus

Yaroslav · Accepted Answer · 2012-10-02 13:44:58Z

38

select distinct id, title, time
  from table t1
 where exists (select * 
                 from table t2 
                where t2.id <> t1.id
                  and t2.title = t1.title
                  and t2.time = t1.time
               )

edited Oct 2, 2012 at 13:44

Yaroslav

6,55210 gold badges54 silver badges91 bronze badges

answered Oct 2, 2012 at 13:39

Wolfgang

4,9352 gold badges32 silver badges29 bronze badges

1 Comment

mwotton Over a year ago

This actually answers the question, returning all three duplicate rows, whereas the other answer only returns one row.

Collectives™ on Stack Overflow

How to find duplicate rows based on multiple fields in MySQL? [duplicate]

2 Answers 2

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Linked

Related