Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345

Deleting Duplicate Records Expand / Collapse
Author
Message
Posted Monday, November 24, 2008 9:44 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, April 17, 2014 5:22 AM
Points: 446, Visits: 649
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]

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

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
Posted Monday, November 24, 2008 12:46 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:07 PM
Points: 2,382, Visits: 3,369
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
Posted Monday, November 24, 2008 12:48 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:07 PM
Points: 2,382, Visits: 3,369
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
Posted Tuesday, November 25, 2008 2:09 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, April 17, 2014 5:22 AM
Points: 446, Visits: 649
Thank you...
Post #608153
Posted Monday, July 16, 2012 5:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase «««12345

Permissions Expand / Collapse