Eliminate Duplicate rows

Question

Eliminate Duplicate rows

Viewing 15 posts - 16 through 30 (of 35 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Certainly not the best way of doing it, Anders, but since you and I have both asked how he decides which duplicate rows to keep and haven't had a proper answer, it's the best anyone can do with the information available.

John

Sam-246632 Old Hand Points: 366 More actions · Answer 2

It does not matter which row to keep and which row to avoid. I just need the record out of the dups.

Thanks

San.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 3

>>It does not matter which row to keep and which row to avoid.

OK. So, for these 2, maybe you can answer some very specific questions to get this wrapped up.

PART_ID DATE PART_CD REN_CD STATE BN_NO BN_CD BN_DS DESC ID

206544 2006-01-01 DDD 11 TX 45789 TIN OTHER NULL 500

206544 2006-01-01 DEA 11 TX 45789 TIN OTHER NULL 500

The columns whose values are in green represent the "primary key", but there are cases of duplicates. Correct ?

The column whose values are in red represent the column which contains a differing value for each "primary key". Correct ?

So what you need to do is remove one of those records, and it doesn't matter whether it's the record with "DDD" as the PART_CD or the record with "DEA" that gets removed. Correct ?

Mark Shvarts 1 SSCertifiable Points: 5702 More actions · Answer 4

alter

table emp add row_number int identity

go

delete

emp

from

emp e

left

join ( select

rnum

= min(row_number),

empid

from emp

group by empid ) t

on

e.row_number = t.rnum

where

t.rnum is null

go

alter

table emp drop column row_number

go

select

* from emp

Sam-246632 Old Hand Points: 366 More actions · Answer 5

Thanks PW and Mark.

PW's remarks:

The columns whose values are in green represent the "primary key", but there are cases of duplicates. Correct ?

The columns PART_ID DATE PART_CD REN_CD are part of PK

The column whose values are in red represent the column which contains a differing value for each "primary key". Correct ? That is correct

So what you need to do is remove one of those records, and it doesn't matter whether it's the record with "DDD" as the PART_CD or the record with "DEA" that gets removed. Correct ? That is correct

Thanks for your help.

San.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 6

Cue gnashing of teeth ...

The columns PART_ID DATE PART_CD REN_CD are part of PK

But, but ... if PART_CD is part of the key, then in the example you gave, the records with "DDD" and "DEA" are already unique, and you don't have an issue of duplicates.

Therefore:

So what you need to do is remove one of those records, and it doesn't matter whether it's the record with "DDD" as the PART_CD or the record with "DEA" that gets removed. Correct ? That is correct

If PART_CD is part of the pkey, and is not duplicated, then your answer of "That is correct" makes no sense - why would you want to delete 1 of those records, if they aren't duplicated according to the primary key ?

Sam-246632 Old Hand Points: 366 More actions · Answer 7

Hi PW,

Thanks for the reply. But I have 2 records with the same PART_ID and I want one record with the unique PART_ID.

Any help would be greatly appreciated.

Thanks

San.

Sam-246632 Old Hand Points: 366 More actions · Answer 8

Any suggestion would be greatly apprecited.

Thanks

San.

Carlos Pereyra Ten Centuries Points: 1069 More actions · Answer 9

Would the top function help? as in select top (1).....

Regards,

Carlos

Sam-246632 Old Hand Points: 366 More actions · Answer 10

Sam-246632

Old Hand

Points: 366

March 28, 2007 at 10:25 am

#697476

Like SELECT TOP(PART_ID)

Thanks

San.

Anders Pedersen SSChampion Points: 11410 More actions · Answer 11

have you even tried the code I posted? It should do what you are saying you need. It could be optimized a bit by using top 1 after using a distinct select to populate the cursor, but you should be able to use as is with your own tables and columns put in.

Sam-246632 Old Hand Points: 366 More actions · Answer 12

Hi Anders,

Thanks for the reply. I cannot use a cursor or Stored Proc and I dont have access rights to create a TEMP table. I need a Sql which I am using in a DTS Package.

Any help would be greatly appreciated.

Thanks

San.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 13

NEW INFORMATION?

You are using DTS to determine the rows to exclude?

You always can create a TEMP table.

Please try the suggestions first before dissing them!

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 14

Try this SQL Server 2005 approach

SELECT

PART_ID,

DATE,

PART_CD,

REN_CD,

[STATE],

BN_NO,

BN_CD,

BN_DS,

[DESC],

ID

FROM (

SELECT *,

ROW_NUMBER() OVER (PARTITION BY PART_ID ORDER BY PART_CD) AS RecID

FROM Table1

) AS x

WHERE RecID = 1

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

Sam-246632 Old Hand Points: 366 More actions · Answer 15

Hi peter,

Thanks for the reply. I amusing Sql Server 2000. Any help would be greatly appreciated.

Thanks

San.