Selecting duplicate rows

[dreamwest]

Sometimes i have rows that are duplicates - but with different primary ids

 

Table name: photo

Column name: title

 

I need to select only the rows that are duplicates

 

I tried a distinct query but it just return all rows

SELECT DISTINCT title FROM photo 

[aschk]

So which fields are duplicated? This is key to the query.

I'll take a guess:

 

SELECT p1.id, p1.title, p2.id, p2.title
FROM photo p1
JOIN photo p2 ON p1.title = p2.title AND p2.id != p1.id

 

The above should give you all the id's (assuming "id" is the name of your primary key) and title's of duplicated records.

 

This also points to an issue with indexes. If your titles need to be unique make sure your application checks for duplicate titles before it starts the insert, AND you have a UNIQUE index on the title field, to stop duplicate entries.