Delete Duplicates/Leave One Record

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 2005 Forums

Transact-SQL (2005)

Delete Duplicates/Leave One Record

Author

Topic

pr4t3ekd
Starting Member

31 Posts

Posted - 2009-10-18 : 21:43:21

I have the following code which gives me duplicates.


select * 
from child
where Supporter_id in (804007953,804444784,804450674,804278174,804490480,804597151,804604403) and
Child_id in (select Child_id from child group by Child_id, child_name, supporter_id, product_type having count(*) >1)
order by supporter_id

how can i modify this code so it deletes all duplicates except one record

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-18 : 21:50:16

[code]
delete d
from
(
select *, row_no = row_number() over ( partition by Child_id, child_name, supporter_id, product_type order by supporter_id)
from child
where Supporter_id in (804007953,804444784,804450674,804278174,804490480,804597151,804604403)
) d
where row_no > 1[/code]

KH
[spoiler]Time is always against us[/spoiler]

pr4t3ekd
Starting Member

31 Posts

Posted - 2009-10-18 : 22:30:57

i get an error saying row_number is not a recognized function name

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-10-19 : 07:58:14

CREATE TABLE TestTable
(TT_id INT ,
tt_name varchar(50),
tt_datetime datetime)

INSERT INTO TestTable
(TT_ID,tt_name,tt_datetime)
SELECT 1,'ABC','2009-07-13 12:02:45.897'
union all
SELECT 1,'ABC','2009-07-13 12:02:45.897'
union all
SELECT 2,'ABC','2009-07-13 12:02:45.897'
union all
SELECT 2,'ABC','2009-07-13 12:02:45.897'
union all
SELECT 3,'ABC','2009-07-13 12:02:45.897'
union all
SELECT 3,'ABC','2009-07-13 12:02:45.897'
union all
SELECT 3,'ABC','2009-07-13 12:02:45.897'

----view your duplicate records
SELECT * FROM TestTable
----delete duplicate rows
WITH T1 AS (SELECT ROW_NUMBER ( ) OVER ( PARTITION BY TT_ID, tt_name, tt_datetime ORDER BY TT_ID ) AS RNUM FROM TestTable )
SELECT * FROM T1 WHERE RNUM > 1

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-20 : 00:44:17

quote:
Originally posted by pr4t3ekd

i get an error saying row_number is not a recognized function name

ROW_NUMBER() is an analytical function and is available SQl Server 2005 onwards. Which version you are running in ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources