Eliminating duplicate rows

Question

Post reply

Eliminating duplicate rows

Viewing 6 posts - 16 through 20 (of 20 total)

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

SwePeso SSC-Dedicated Points: 39725 More actions · Answer 1

DELETE f

FROM (SELECT ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY ID) AS RecID) AS f

WHERE RecID > 1

N 56°04'39.16"
E 12°55'05.25"

Bharat Shah-487869 Grasshopper Points: 15 More actions · Answer 2

Hi Guys

This is my first post to this forum. Here is my solution .

I have table named duplicate in which RID is identity column and V1, V2, V3 other column which are having duplicate values.

I wrote this query

delete from duplicate where RID not in

( select a.RID from

( select min (RID) as RID , V1, V2, V3 from duplicate group by V1, V2, V3 ) a )

enjoy ....

bharat Shah

bharat@thegt.com

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Now... that's a bummer...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

rclare@factset.com SSC-Addicted Points: 406 More actions · Answer 4

So basically you deleted all of the rows that didn't have foreign key relationships?

I don't think using min(ID) would work since the ID column is a GUID right?

zach-435178 Old Hand Points: 383 More actions · Answer 5

ok I have a fix that makes sense now...

I deleted out of my image table where manCode was equal to the manufacturer that had duplicate rows.

Then I re-insert the rows CORRECTLY this time and use an update statement to relate the tables back to each other.

I was fortunate enough to have back up of the data.

I guess sometimes its just better to start fresh rather than try to clean up a mess, and now that I look back on it this was the easiest way to do so.

and yes ryan the mid() doesn't work because of the GUID

thanks for everyones input

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

Nice job, Zach... didn't realize that starting over was an option but I do thank you for the explanation of what you did

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions