Hi Guys,,,
I want to delete duplicate records from my table and there is no primary key which i can use in this and all the values in rows are identical.
identity name
1 Sumit
2 Ajay
So i just want to get only 2 rows in this
This is dummy data only i have many other columns in my table
and i am also not able to use ranking functions as still we are on SQL 2000
Hope you guys help.
Divya Agrawal
I formatted your code for better readability
But your method is the most ineffecient among any method that remove duplicate
For the given examples, it would be
select distinct * from table
Madhivananhttp://beyondrelational.com/blogs/madhivanan
declare @tab table (id int,name varchar(10))insert into @tab (id,name)select 1,'Sumit' union all select 1,'Sumit' union all select 2,'Sumit2' union all select 2,'Sumit2' select id,name from ( select row_number() over (partition by id,name order by id )as row ,id,name from @tab )as t where row= 1
Thanks Bhavani,, But here is the problem that i am using SQL Server 2000 in which row_number() function is not available.
Do you have any way to do it in SQL Server 2000
Declare @tbl Table(name varchar(100), number int,id int) insert into @tbl select 'divya',111,1 union all select 'divya',211,1 union all select 'divya',311,1 union all select 'srinu',111,2 union all select 'srinu',211,2 union all select 'srinu',311,2 set rowcount 1 while exists ( select id from @tbl group by id having count(*) > 1) begin delete from @tbl where id in(select top 1 id from @tbl group by id having count(*) > 1) end set rowcount 0 select * from @tbl There can be other method by adding an identity column Declare @tbl Table(name varchar(100), number int,id int) insert into @tbl select 'divya',111,1 union all select 'divya',211,1 union all select 'divya',311,1 union all select 'srinu',111,2 union all select 'srinu',211,2 union all select 'srinu',311,2 set rowcount 1 while exists ( select id from @tbl group by id having count(*) > 1 ) begin delete from @tbl where id in(select top 1 id from @tbl group by id having count(*) > 1) end set rowcount 0 select * from @tbl
Another method can be by adding any identity column.
--Never expect yourself to be given a good value... create a value of your own
This is the most common question you will in the forums
http://www.sql-server-performance.com/articles/dba/delete_duplicates_p1.aspx
http://support.microsoft.com/kb/139444
http://www.sqlteam.com/article/deleting-duplicate-records
;
with
as
(
)
Delete
from Dups where rownum > 1 ; SELECT
SELECT
* FROM employee