deleting duplicate rows but based on some conditio

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

SQL Server Development (2000)

deleting duplicate rows but based on some conditio

Author

Topic

sachingovekar
Posting Yak Master

101 Posts

Posted - 2008-10-10 : 13:18:57

Hi all,

I have a table which are duplicate rows but not all columns are duplicate for eg:

userid username application appid appdate vendor version
s0001 sharon adobe reader ar001 2005-06-09 adobe 7.0
s0001 sharon adobe reader ar001 2006-09-03 adobe 6.0
s0001 sharon adobe reader ar002 2005-03-04 adobe 8.0
s0001 sharon adobe reader ar005 2004-09-08 adobe 3.0
p0001 pamela winzip wz001 2003-04-08 winzip 3.0
a0001 autin winzip wz001 2003-04-08 winzip 3.0
a0001 dukes winzip wz001 2003-04-08 winzip 3.0

i want to delete users which are repeated and appid is repeated and keep only one row.

for eg: in table above sharon has two entries for appid ar001 .
i want to keep any one record of ar001.

I my table there are many records, is there any possible way to write a script for this.

Kindly HELP

Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-10 : 13:38:40

[code]DELETE t
FROM YourTable t
LEFT JOIN (SELECT userid,appid,max(appdate) as latest
FROM yourtable
GROUP BY userid,appid) t1
ON t1.userid=t.userid
AND t1.appid=t.appid
AND t1.latest=t.appdate
WHERE t1.userid IS NULL[/code]

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-10 : 13:40:18

Having an identity column would be nice you know...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources