Selecting Duplicate values

Discussion in 'MySQL' started by Silver89, Jun 9, 2011.

0
  1. #1
    If I have a table like the following:

    [​IMG]

    and I want to select the highlighted rows because they are duplicates (values are the same apart from id)

    then how could/would I do this?

    So rows 1,3,4,5,9,10 would be returned
     
    Silver89, Jun 9, 2011 IP
  2. rayqsl

    rayqsl Active Member

    Messages:
    91
    Likes Received:
    0
    Best Answers:
    1
    Trophy Points:
    53
    #2
    something like this should do it

    select t.*
    from <tablename> t
    join
    (select col2, col3, itemid, count(*)
    from <tablename>
    group by col2, col3, itemid
    having count(*) > 1) m on m.col2 = t.col2 and m.col3 = p.col3 and m.itemid = t.itemid
     
    rayqsl, Jun 9, 2011 IP
  3. unknownpray

    unknownpray Active Member

    Messages:
    3,831
    Likes Received:
    14
    Best Answers:
    0
    Trophy Points:
    70
    #3
    SELECT *
    FROM table
    WHERE tablefield IN (
    SELECT tablefield
    FROM table
    GROUP BY tablefield
    HAVING (COUNT(tablefield ) > 1)
    )
     
    unknownpray, Jun 24, 2011 IP