CODEWITHSUNDEEP
mysqlsqlselect
ruhacz
ruhacz

Reputation: 3

Selecting all duplicates from table

I'v got a table with columns like this:

serial_id   season  ep_id
1           1       1   < duplicate
1           1       2
3           1       1
...
1           1       1   < duplicate

I want to select all rows that have a duplicated values in serial_id, season and ep_id , what query i should use? Thanks.

Upvotes: 0

Views: 97

Answers (2)

John Woo
John Woo

Reputation: 263683

"..duplicated values in serial_id, season and ep_id."

SELECT  serial_id, ep_id, season
FROM    tableName
GROUP   BY serial_id, ep_id, season
HAVING  COUNT(*) > 1

but if you want to get all columns (*assuming you have other columns except serial_id, ep_id, season*) within the row

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT  serial_id, ep_id, season
            FROM    tableName
            GROUP   BY serial_id, ep_id, season
            HAVING  COUNT(*) > 1
        ) b ON  a.serial_id = b.serial_id AND
                a.ep_id = b.ep_id AND
                a.season = b.season

Upvotes: 2

Dan Bracuk
Dan Bracuk

Reputation: 20794

something like this:

select serial_id, season, ep_id, count(*) records
from yourtable
group by serial_id, season, ep_id
having count(*) > 1

Upvotes: 1

Related Questions