How to Remove duplicate records (rows)

Question

Post reply

How to Remove duplicate records (rows)

wmalik001

Ten Centuries

Points: 1190
More actions
May 20, 2010 at 7:46 am

#97372

There is a table, with a primary key with the data type of uniqueidentifier and the rest of the columns are of data type varchar, i am using the following query to remove duplicates:

DELETE

FROM dbo.SMRights

WHERE gRightSID NOT IN

(

SELECT MAX(gRightSID)

FROM dbo.SMRights

GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3,Duplicatecolumn4,Duplicatecolumn5)

The table name is SMRights and the uniqueidentifier column is gRightSID, the other columns are the ones which have duplicate rows against the gRightSID.

Duplicatecolumn1 2 and 3 are of data type varchar

Duplicatecolumn4 and 5 are of data type text

When I run this I get a message ::: Operand data type uniqueidentifier is invalid for max operator.

Operand data type uniqueidentifier is invalid for max operator.

Please let me know how i can remove the duplicate records.

Thanks & Best Regards

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Horatiu SSChasing Mays Points: 602 More actions · Answer 1

DELETE

FROM dbo.SMRights

WHERE gRightSID NOT IN

(

SELECT a.gRightSID

FROM (select row_number() over( prtition by DuplicateColumn1, DuplicateColumn2, DuplicateColumn3,Duplicatecolumn4,Duplicatecolumn5 order by Duplicatecolumn5) as rownum, gRightSID from dbo.SMRights ) a where a.rownum=2)

hope it works.

There is always something new to learn.
My personal SQL Blog[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

;WITH SequencedTable AS (



SELECT GroupID = ROW_NUMBER() OVER (PARTITION BY DuplicateColumn1, DuplicateColumn2, ...)

FROM dbo.SMRights)

DELETE FROM SequencedTable WHERE GroupID > 1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

wmalik001 Ten Centuries Points: 1190 More actions · Answer 3

wmalik001

Ten Centuries

Points: 1190

May 20, 2010 at 9:17 am

#1170201

Hi gheorghiu_horatiu,

There is a problem at the last statement near the where clause

a where a.rownum=2)

wmalik001 Ten Centuries Points: 1190 More actions · Answer 4

Guys, thank you for your reply,

But i am getting this error:

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

Msg 306, Level 16, State 2, Line 7

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

Its so confusing and i am stuck 🙁

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 5

Steve Jones - SSC Editor

SSC Guru

Points: 728159

May 20, 2010 at 9:23 am

#1170213

You could run that multiple times to clear all rows, which is something to do if you are managing log sizes or worried about activity with a large transactions.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

wmalik001 (5/20/2010)

Guys, thank you for your reply,

But i am getting this error:

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

Msg 306, Level 16, State 2, Line 7

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

Its so confusing and i am stuck 🙁

Please provide the script for table SMRights

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

wmalik001 Ten Centuries Points: 1190 More actions · Answer 7

wmalik001

Ten Centuries

Points: 1190

May 20, 2010 at 9:36 am

#1170227

So you are saying that these error messages are ignorable? and I should keep running the query and it will remove the duplicates from the table?

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 8

Steve Jones - SSC Editor

SSC Guru

Points: 728159

May 20, 2010 at 9:38 am

#1170232

No, those error messages are based on your data types. You can't submit those commands on those types.

Please post some DDL for the tables.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

wmalik001 Ten Centuries Points: 1190 More actions · Answer 9

Ok the first one i used is :

DELETE

FROM dbo.SMRights

WHERE gRightSID NOT IN

(

SELECT a.gRightSID

FROM (select row_number() over( partition by cRightID,cDesc,cDataDef,cGroup,cHelp,cDataHelp

order by cHelp) as rownum, gRightSID from dbo.SMRights)

a where a.rownum=2)

I got the error :

Msg 306, Level 16, State 2, Line 6

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

Msg 306, Level 16, State 2, Line 6

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

Msg 306, Level 16, State 2, Line 7

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

Then i used Chris's script

WITH SequencedTable AS (

SELECT GroupID = ROW_NUMBER() OVER (partition by cRightID,cDesc,cDataDef,cGroup,cHelp,cDataHelp

order by cHelp)

FROM dbo.SMRights)

DELETE FROM SequencedTable WHERE GroupID > 1

And got the same errors as above;

The table(SMRights) is as follows:

Columns:

gRightSID(PK,uniqueidentifier,not null)

cRightID(varchar(50),not null)

cDesc(varchar(50),not null)

cDataDef(varchar(1024),not null)

cHelp(text,not null)

cDataHelp(text,not null)

wmalik001 Ten Centuries Points: 1190 More actions · Answer 10

All i want to do is to remove the duplicate records from these columns:

Columns:

gRightSID(PK,uniqueidentifier,not null) ----> this one has not duplicate record

cRightID(varchar(50),not null)---> if this column has duplicate records then delete that entire row

cDesc(varchar(50),not null)

cDataDef(varchar(1024),not null)

cHelp(text,not null)

cDataHelp(text,not null)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

;WITH SequencedTable AS (



SELECT GroupID = ROW_NUMBER() OVER (PARTITION BY cRightID ORDER BY cDesc)

FROM dbo.SMRights)

DELETE FROM SequencedTable WHERE GroupID > 1

-- Note that the choice of sort column is up to you!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

wmalik001 Ten Centuries Points: 1190 More actions · Answer 12

wmalik001

Ten Centuries

Points: 1190

May 20, 2010 at 11:55 am

#1170373

Thank You Chris, looks like this is the solution, once again thank you so much for your time in helping me out, your the man!

Horatiu SSChasing Mays Points: 602 More actions · Answer 13

Horatiu

SSChasing Mays

Points: 602

May 20, 2010 at 11:34 pm

#1170593

wmalik001, you should not use text data type, unless you use sql server 2000. You should use varchar(max)...

There is always something new to learn.
My personal SQL Blog[/url]

carolwood Old Hand Points: 317 More actions · Answer 14

Hello Friends.......

To delete similar records, i.e. where the records are not the same but one field is the same and only one copy needs to be preserved, try the following SQL:

delete T1

from MyTable T1, MyTable T2

where T1.dupField = T2.dupField

and T1.uniqueField > T2.uniqueField

This will delete all records from the table MyTable which have the same value for the field dupField, leaving that record which has the lowest value in uniqueField.

Thanks

web development