Check for duplicate text in multiple rows 3

[hapax]

I have the following table:

CREATE TABLE NoteTest
(
[ItemId] [int](10) NULL,
[Note] [nchar](10) NULL
)

Each ItemId should only have one note row in the table. But for some items, multiple rows with identical notes were entered.

I need SQL to go through the NoteTest table and identify identical, duplicate text in the Note column for each ID in the table.

 

[markros]

select T.* from myTable T inner JOIN (select Note, count(*) as DupsCount from myTable group by Note having count(*) > 1) Dups on T.Note = Dups.Note

 

[hmckillop]

Have a look at

http://www.15seconds.com/issue/011009.htm

Has exactly what you need.

Alternatively here is an untested version

/* 1.  Query heading */
SELECT
  *  /* Remember to never use SELECT * and write out the field names */
FROM
  /* 2.  Table with duplicates */
  NoteTest
WHERE
  EXISTS  (
    SELECT
      NULL
    FROM
      /* 3.  Table with duplicates, with an alias */
      NoteTest b
    WHERE
      /* 4.  Join each field with *itself*.  These are fields that could be Primary Keys */
      b.[Note] = NoteTest.[Note]
    GROUP BY
            /* 5.  I must GROUP BY these fields because of the HAVING
         clause and because these are the possible PK */
      b.[Note]
    HAVING
      /* 6.  This is the determining factor.  We can control our
         output from here.  In this case, we want to pick records
         where the ID is less than the MAX ID */
      NoteTest.[ItemId] < MAX(b.[ItemId])
  )

"I'm living so far beyond my income that we may almost be said to be living apart

 

[gmmastros]

This should return your duplicates.

Select ItemId, Note
From   NoteTest
Group by ItemId, Node
Having Count(*) > 1

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[hmckillop]

lol, beat to the finish line!

"I'm living so far beyond my income that we may almost be said to be living apart

 

[markros]

George - assuming we have the same ID and the Note, not just the same Note for different IDs.

 

[markros]

2 hmckillop - in another forum we were once called vultures It's always many people trying to answer the simple questions

 

[hapax]

You guys Rock! Thanks

 

[hapax]

when I try this one

Select ItemId, Note
From NoteTest
Group by ItemId, Note
Having Count(*) > 1

I get the error:

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Note is a text column. It doesn't like the group by maybe?

 

[gmmastros]

in your original post, Note was nchar(10). now it's a text datatype? Hmm.....

If you are satisfied with checking the first 8000 bytes to see if a note is a duplicate, then you could try this....

Select ItemId, SubString(Note, 1, 8000)
From   NoteTest
Group by ItemId, SubString(Note,1 , 8000)
Having Count(*) > 1

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom