delete duplicate records

saloj
saloj used Ask the Experts™
on
I have the following records on table. I would like to delete duplicate records according to date desc and only keep one record on table.

id      no      text      Date
1      2      xyz      2010-03-08 21:14:21.707
2      2      zzz      2010-02-08 21:14:21.707
3      3      uye      2010-01-08 21:14:21.707
4      3      jah      2010-01-08 21:14:21.707
5      3      zla      2010-01-07 21:14:21.707


id      no      text      Date
1      2      xyz      2010-03-08 21:14:21.707
3      3      uye      2010-01-08 21:14:21.707
any help would be appreciated

Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Take the ID Number to a temp table and delete.

http://support.microsoft.com/kb/139444
Top Expert 2010

Commented:
hi

this will do

select t.* from [Table] t
where id in
(select top 1 [id] from [Table] d where d.[no] = t.[no] order by d.[date1] )
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Top Expert 2010

Commented:
forgot to mention you need to store your result in another table
and then drop first table and rename the new table

step 1

Select t.* into Table2 from [Table1] t
where id in
(select top 1 [id] from [Table1] d where d.[no] = t.[no] order by d.[date1] )

step 2

Drop table Table1

step3
Rename the table2 to table1, you can do this using SSMS

 
try this
;with CTE as (
select *, row_number() over (partition by [no] order by [date] desc) rn from yourtable
)
delete from CTE
where rn <> 1

Open in new window

oh well, I've just noted that you say this  is SQL 2000, then we need to do something there to fix your IDs, because it seems they are not in sequence with your dates. So try the below:
create table #temp (
	secid int identity,
	id int,
	no int,
	text varchar(100),
	[date] datetime
)


insert #temp
select * from yourtable
order by [no], [date] desc


delete from yourtable
left join (
	select * from #temp t1 where t1.secid = (select max(secid) from #temp where [no] = t1.[no])
) b on yourtable.no = b.no and yourtable.id = b.id
where b.no is null

drop table #temp

Open in new window

oops typo
create table #temp (
	secid int identity,
	id int,
	no int,
	text varchar(100),
	[date] datetime
)


insert #temp
select * from yourtable
order by [no], [date] desc


delete from yourtable
left join (
	select * from #temp t1 where t1.secid = (select min(secid) from #temp where [no] = t1.[no])
) b on yourtable.no = b.no and yourtable.id = b.id
where b.no is null

drop table #temp

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial