Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1

    Unanswered: Check For Duplicate Records No Unique ID

    Hi all,

    Using SSE 2012 64-bit

    How can I check all fields for duplicate records?
    I tried OVER PARTITION
    But that is returning an error message

    thx
    w

    Code:
    USE db
    SELECT ROW_NUMBER() OVER (PARTITION BY all fields)
                                                  ORDER BY ID --Not unique) AS RowNumber
    From tbl
    error message:
    The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Would you like me to move this question from the MySQL forum to the Microsoft SQL Server forum? It will probably get more useful answers there.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Post your table definition.

    Do you want a list of rows where all columns are the same on all rows or just specific columns?

  4. #4
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Pat,

    Yes, please.

    Spacebar,

    I ended creating a unique id by concatenating 8 fields together and then using that field in the OVER (PARTITION BY....)

    Worked great, found about 4.3K duplicates in 78K records.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You'll need to explicitly list the columns, and will need to exclude the LOB data types (like NVARCHAR(MAX), VARCHAR(MAX), TEXT, NTEXT, and IMAGE) from the column list, but you can use:
    Code:
    SELECT <column list goes here>
       FROM myTable
       GROUP BY <column list goes here>
       HAVING 1 < Count(*)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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