Removing duplicates without removing from table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Removing duplicates without removing from table

Author

Topic

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-05-08 : 07:39:24

Hi guys,

i'm a little stuck here at the moment so hope someone can help,

I've a table which im changing a code which was 1,2 or 9 to now read 6 however because because some were subscribed to 1,2, and 9 and then other 1,2 and other 9,1 etc its proving hard removing duplicates before updating.

I also can't use a Select distinct into a #temp table, remove rows and then reinsert method, as all the rows all have a uniqueidentifier column on the end which then makes them all distinct.

Hope someone can help with this

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-08 : 07:58:43

It is not easy to understand...
Can you give table structure, sample data and wanted result please?

No, you're never too old to Yak'n'Roll if you're too young to die.

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-05-08 : 08:04:56

This is the table

UserID CODE Date RowGuid
3196229 6 2006-02-15 00:00:00.000 963805A4-DB5B-4379-9901-F4DEDC6F0EC3
3196229 6 2006-02-15 00:00:00.000 53660646-4B15-484C-8575-BCC8F573016C
3196229 6 2006-02-15 00:00:00.000 FF9F16AF-714D-4300-BB78-DF0FD84ED0EB

But i want to have just one of these rows in the table and the others removed, however remember this is in SQL 2000 so there is no OVER function etc

Hope this is more clear

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-08 : 08:11:39

Why is CODE already 6?
Is this right?

No, you're never too old to Yak'n'Roll if you're too young to die.

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-05-08 : 08:14:45

I can change that back but that is what happen during the update to change them to 6 but it could read

UserID CODE Date RowGuid
3196229 1 2006-02-15 00:00:00.000 963805A4-DB5B-4379-9901-F4DEDC6F0EC3
3196229 2 2006-02-15 00:00:00.000 53660646-4B15-484C-8575-BCC8F573016C
3196229 9 2006-02-15 00:00:00.000 FF9F16AF-714D-4300-BB78-DF0FD84ED0EB

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-08 : 08:20:37

Ok now I understand.
Update is already done.
Rows are distinct except in RowGuid.

I will try a solution now...

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-08 : 08:26:51

[code]
delete d
from table d
join (select UserId,CODE,[Date],MAX(RowGuid) as RowGuid
from table group by UserId,CODE,[Date]) t
on d.UserID=t.UserID AND
d.CODE=t.CODE AND
d.[Date]=t.[Date] AND
d.RowGuid <> t.RowGuid
[/code]

Please have a test, but first replace 'table' by your tablename.

Webfred
Edit: initial situation is: CODE is already DISTINCT

No, you're never too old to Yak'n'Roll if you're too young to die.

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-05-08 : 08:33:27

Tested this but it falls down that you can't have a max() on the rowguid

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-08 : 08:43:10

Oh sorry!
Please replace
MAX(RowGuid) as RowGuid
with
MAX(convert(varchar(255),RowGuid)) as RowGuid

No, you're never too old to Yak'n'Roll if you're too young to die.

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-05-08 : 09:02:08

Massive thanks - I was playing around with the max rowguid but never thought of the converting first

Its greate sometimes to have a fresh set of eye to help, hope to help you sometime.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-08 : 09:07:18

My pleasure!

No, you're never too old to Yak'n'Roll if you're too young to die.

atulchakarvarti
Starting Member

2 Posts

Posted - 2009-05-09 : 09:38:58

hi all,

i have a prob in sql... i have some data in excel file and i embedded that data into sql2000 to remove duplicacy!! my data contains name, telephoneno. ! now the prob is i want to remove duplicate phn nos. and names. but in many rows phn nos. are same but names are different.and i want to del the row where phn no. is duplicate.so plz help me to remove duplicacy in sql

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources