Deleting Duplicate Records

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Sureshkumar... » Deleting Duplicate Records

45 posts, Page 5 of 5

«««1 2 3 4 5

Deleting Duplicate Records

Rate Topic

Display Mode

Topic Options

Author

Message

HBhagat

Posted Monday, November 24, 2008 9:44 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 7:58 AM
Points: 406, Visits: 600

Hi All,
I have a problem which everybody is discussing.
I need to find the duplicate record in the table. Its not a matter of just 1 column.
create table #temp
(ddatetime datetime ,
co1 int ,
col2 int )

Insert into #temp
values ('2001-11-12 13:29:00.000' , 101 , 15)
Insert into #temp
values ('2001-11-12 13:45:00.000' , 102 , 15)

Insert into #temp
values ('2001-11-12 13:50:49.000' , 101 , 15) [duplicate] Cool

Insert into #temp
values ('2001-11-12 13:50:49.000' , 101 , 15) [duplicate] Cool

Insert into #temp
values ('2001-11-12 14:00:49.000' , 101 , 15)

Insert into #temp
values ('2001-11-12 14:00:49.000' , 102 , 15)

Insert into #temp
values ('2001-11-12 14:00:49.000' , 103 , 15)

I have to find the duplicate value with the combination of the 3 columns.

Please help

Post #607680

SwePeso

Posted Monday, November 24, 2008 12:46 PM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 30, 2013 1:26 PM
Points: 2,359, Visits: 3,293

Ajay... Yes it does work.

DELETE t1
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col2 DESC) AS RecID
FROM Table1
) AS t1
WHERE RecID > 1

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

Post #607812

SwePeso

Posted Monday, November 24, 2008 12:48 PM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 30, 2013 1:26 PM
Points: 2,359, Visits: 3,293

harsha.bhagat1

SELECT ddatetime, col1, col2
FROM (select ddatetime, col1, col2, row_number() over (partition by ddatetime, col1, col2 order by ddatetime) as recid
from table1
) as d
where recid > 1

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

Post #607813

HBhagat

Posted Tuesday, November 25, 2008 2:09 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 7:58 AM
Points: 406, Visits: 600

Thank you...

Post #608153

weblorquins

Posted Monday, July 16, 2012 5:52 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, July 16, 2012 5:50 AM
Points: 2, Visits: 0

Delete From TableName
Where ID Not IN
{
Select MAX(ID) From TableName
Group By Col1, Col2...
}

By The help of Table name useing ID and the Group By we can delete the duplicate record from table in sql server.

For more Interview question Click on billow link.

http://sqlserver4us.blogspot.in/

Post #1330046

« Prev Topic | Next Topic »

45 posts, Page 5 of 5

«««1 2 3 4 5

Permissions