Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: Select All Duplicates

    Just wanted to ask how to get all the duplicates records in a table. If I have say the following:
    col1 col2 col3 col4 col5
    1 A1 ABC A21 AJ
    1 A1 ABC A21 AJ
    1 A2 ABC A21 AJ

    The query should return the first 2 identical rows. I tried the following form but as you can see it has flaw that it gets the 3rd row as well simply because the outer select uses col1 as a condition which could belong to a "not completely" identical row.
    The inner select results in the distinct duplicate rows (2 in the table above, either of row 1 or 2 and row 3).

    select *
    from table1
    where col1 IN
    (select col1
    from table1
    group by col1, col2, col3, col4, col5
    having count(*) > 1
    )

    Thank you for any help and have a great week end!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select col1, col2, col3, col4, col5
    from table1
    group by col1, col2, col3, col4, col5
    having count(*) > 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2006
    Posts
    111
    hi rudy,

    thanks for the answer. however, i think the result set of your answer will be each distinct row of all duplicated rows. however what i have in mind is returning all the rows of each duplicated rows so in my sample table below i should get (both rows 1 AND 2) rather than (either row 1 OR 2 AND row 3)
    regards!

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If they are a duplicate then rows 1 and 2 will be the same.

    Perhaps:
    Code:
    select col1, col2, col3, col4, col5
    from
    (
    select col1, col2, col3, col4, col5
    from table1
    group by col1, col2, col3, col4, col5
    having count(*) > 1
    ) as dupes
    cross join
    (select 1 as duper
    union all
    select 2) as dupetable
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2006
    Posts
    111
    i'm just trying out this latest code. will let you know soon if it worked! thanks!

    edit:
    I can't even save the sql, it's having error with FROM clause. hmmn might have to try a different tac. also, yes rows 1 and 2 are the same but they are different records and should therefore be reported by my query.
    regards!
    Last edited by g11DB; 07-05-07 at 11:15.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    just a real simple question before you go too much further, g11DB

    the solutions you are getting are for a table with exactly 5 columns in it, as described by you

    so, how many columns are there, really?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2006
    Posts
    111
    sorry i didn't give enough details. the table above is just an example to make the problem easier to explain. my actual table contains almost a hundred thousand records. It has a considerable number of duplicate records which I will remove but the client would like to have a report of the actual duplicates first (and how many there are of each one, not just the count but the actual records) thanks and regards!

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    My post was a bit of a joke. The idea was that as I understand it you want all duplicates. You want to see all rows. But if the rows are duplicates then you will get each row simply repeated.

    CREATE TABLE me (myName)

    INSERT me SELECT 'poots'
    INSERT me SELECT 'poots'

    Rudy would return:
    poots

    You want:
    poots
    poots

    Does that not seem a bit pointless?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Or do you mean you want duplicates of the primary key?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jul 2006
    Posts
    111
    i originally came up with the same code as rudy suggested but the client wanted to see the version i am asking for now. i must admit the reason now why i want to pursue it is it's a bit of a challenge and i would like to see how this could be done. regards!

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Either the requirement is daft or you have not explained the problem properly.

    Code:
    
    CREATE TABLE dbo.table1
    (col1 int
    , col2 int
    , col3 int
    , col4 int
    , col5 int)
    
    INSERT INTO table1
    SELECT 1, 1, 1, 1, 1
    UNION ALL 
    SELECT 1, 1, 1, 1, 1
    UNION ALL 
    SELECT 1, 1, 1, 1, 1
    UNION ALL 
    SELECT 2, 2, 2, 2, 2
    UNION ALL 
    SELECT 2, 2, 2, 2, 2
    
    SELECT col1, col2, col3, col4, col5
    FROM (SELECT col1, col2, col3, col4, col5,COUNT(*) AS no_rows
    FROM dbo.table1
    GROUP BY col1, col2, col3, col4, col5
    HAVING COUNT(*)> 1)AS the_dupes
    INNER JOIN 
    dbo.numbers
    ON number BETWEEN 1 AND no_rows
    
    DROP TABLE dbo.table1
    
    You can get a numbers table (Very useful!) from here:
    http://sqljunkies.com/WebLog/amachan...bersTable.aspx
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •