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.

Answer 1

Here is what you want

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

Answer 2

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                )