Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Location
    Kentucky
    Posts
    19

    Unanswered: How to find duplicate rows in SQL server

    I would like to locate duplicate rows within a specific table. This table has 12 diffrent rows.

    BASENO - POSITION - SEQ - PROD -STYL - DESCR - FIELD01 THRU FIELD05 - VALUE01 THRU VALUE05 - FORMTYPE - ANSWER

    I have been playing with the following query but can't seem to get it perfect to locate my dups within sql. Can someone help me with the querry?

    I'm playing with the following querry.
    SELECT
    <list of all columns>
    FROM
    tablename
    GROUP BY
    <list of all columns>
    HAVING
    Count(*) > 1

    Can somone possible input my column names into this querry that would possibly get it to locate my dups? I'm missing somehting and not sure what.

    Thanks for any help

    SQL Newbie

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you simply want to eliminate all of the rows that have a duplicate (every one of them, leaving none behind at all), you can use:
    Code:
    DELETE FROM tablename
       WHERE 1 < (SELECT Count(*)
          FROM tablename AS z
          WHERE  z.baseno = tablename.baseno
             AND z.position = tablename.position
    -- continue for all columns
       )
    This is rarely what people want, since they usually want to keep one of the rows. That is a tougher challenge.

    -PatP

  3. #3
    Join Date
    Feb 2004
    Posts
    193
    I had a situation where I needed to delete the duplicate values (there may be more than one) and keep the minimum (or first) value. Assuming you have an id value and BRANCHNO is your duplicate field:

    Code:
    SELECT id
    FROM tablename
    WHERE id
      IN (SELECT a.id
              FROM tablename AS a, tablename AS b
              WHERE a.BRANCHNO = b.BRANCHNO
              AND a.id > b.id);
    Not 100% sure but give it a shot.

    ddave

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    To simply identify the duplicated values, this is the syntax:

    SELECT BASENO, POSITION, SEQ, PROD, STYL, DESCR, FIELD01, FIELD02, FIELD03, FIELD04, FIELD05, VALUE01, VALUE02, VALUE03, VALUE04, VALUE05, FORMTYPE, ANSWER
    FROM tablename
    GROUP BY
    BASENO, POSITION, SEQ, PROD, STYL, DESCR, FIELD01, FIELD02, FIELD03, FIELD04, FIELD05, VALUE01, VALUE02, VALUE03, VALUE04, VALUE05, FORMTYPE, ANSWER
    HAVING Count(*) > 1
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ooops, my bad. I misread the question thinking that you wanted to DELETE the rows, not just see them. Sorry.

    -PatP

  6. #6
    Join Date
    Mar 2004
    Location
    Kentucky
    Posts
    19
    Quote Originally Posted by Pat Phelan
    If you simply want to eliminate all of the rows that have a duplicate (every one of them, leaving none behind at all), you can use:
    Code:
    DELETE FROM tablename
       WHERE 1 < (SELECT Count(*)
          FROM tablename AS z
          WHERE  z.baseno = tablename.baseno
             AND z.position = tablename.position
    -- continue for all columns
       )
    This is rarely what people want, since they usually want to keep one of the rows. That is a tougher challenge.

    -PatP
    I used your information above and I recieved a relply that (0 row(s) affected).
    However when I try and set my primary keys and allow NUULS options I get an error message about duplicates. (below)

    'TableName' table
    -unable to create index 'PK_TableName'.
    ODBC error CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is 415162

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ah, that is quite differentthan the original question though. Now you want to find duplicates based on just the PK column(s)!

    To find the rows with duplicate key values, you need to use something like:
    Code:
    SELECT pk_col1, pk_col2, pk_colN
       FROM tablename
       WHERE 1 < (SELECT Count(*)
          FROM tablename AS z
          WHERE  z.pk_col1 = tablename.pk_col1
             AND z.pk_col2 = tablename.pk_col2
             AND z.pk_colN = tablename.pk_colN)
    Which column(s) are your candidate key?

    -PatP

  8. #8
    Join Date
    Mar 2004
    Location
    Kentucky
    Posts
    19
    Quote Originally Posted by Pat Phelan
    Ah, that is quite differentthan the original question though. Now you want to find duplicates based on just the PK column(s)!

    To find the rows with duplicate key values, you need to use something like:
    Code:
    SELECT pk_col1, pk_col2, pk_colN
       FROM tablename
       WHERE 1 < (SELECT Count(*)
          FROM tablename AS z
          WHERE  z.pk_col1 = tablename.pk_col1
             AND z.pk_col2 = tablename.pk_col2
             AND z.pk_colN = tablename.pk_colN)
    Which column(s) are your candidate key?

    -PatP
    Thanks for your help. I really appreciate your time. i will give it a try.

  9. #9
    Join Date
    Oct 2013
    Posts
    1

    RE: How to identify duplicate

    Would the following syntax work?

    SELECT [PK_ID], COUNT(PK_ID)
    FROM [Table]
    GROUP BY [PK_ID]
    HAVING COUNT(PK_ID) > 1

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This thread is nine years old.
    These days, there are more effective and useful means of identifying duplicates by using row aggregate functions and common table expressions.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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