Answer / victor

this can help if you want to keep only different records

create table test
(
id int,
name varchar(20)
)

insert into test VALUES(1,'test')
insert into test VALUES(2,'test')
insert into test VALUES(2,'test')
insert into test VALUES(3,'test')
insert into test VALUES(4,'test')
insert into test VALUES(5,'test')
insert into test VALUES(6,'test')
insert into test VALUES(7,'test')
insert into test VALUES(7,'test')
insert into test VALUES(7,'test')



select * from test order by 1

while @@rowcount != 0
begin
delete top (1) test where id in
(
select id
FROM test
GROUP BY id having count(id)>1
)
end

select * from test order by 1


-- At the end you will have only differents IDS

Answer / onlymoid

CREATE TABLE dbo.duplicateTest ------Deleting
Duplicates with same id
(
[ID] [int] ,
[FirstName] [varchar](25),
[LastName] [varchar](25)
) ON [PRIMARY]

INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')
INSERT INTO dbo.duplicateTest VALUES(2, 'Dave','Jones')
INSERT INTO dbo.duplicateTest VALUES(3, 'Karen','White')
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')



SELECT * FROM dbo.duplicateTest

SET ROWCOUNT 1
DELETE FROM dbo.duplicateTest WHERE ID = 1
SET ROWCOUNT 0

SELECT * FROM dbo.duplicateTest

Drop table dbo.duplicatetest

Answer / smitha

;with empctc(empid,ename,sal,deptid,ranking)
as
(Select empid,ename,sal,deptid,ranking=Dense_rank() over (
partition by empid,ename,sal,deptid order by NEWID() asc)
from emp
)
delete * from empctc where ranking>1

Answer / manjeet kumar

delete from table_name where column_name='value' and rowid
not in (select max(rowid) from table_name where
column_name='value');

e.g. create table duplicate (name varchar(15), rollno number
(10));

insert into duplicate (name,rollno) values ('mkumar',2);
insert into duplicate (name,rollno) values ('mkumar',2);

delete from duplicate where name='mkumar' and rowid not in
(select max(rowid) from duplicate where name='mkumar');

Answer / debasish

while @@rowcount != 0
begin
delete top (1) test where columnname in
(
select columnname
FROM tablename
GROUP BY columnname having count(*)>1
)
end

Answer / eashwar v

IN SQL SERVER 2005, This can be easily achieved without
crating unique identifier by using CTE and ROW_NUMBER (),
the modified query for sql server 2005 goes here
***********************************************
WITH T1 AS (SELECT ROW_NUMBER ( ) OVER ( PARTITION BY ID,
FNAME, LNAME ORDER BY ID ) AS RNUM FROM DUPLICATE )
DELETE FROM T1 WHERE RNUM > 1
***********************************************
To get a detail grasp on ROW_NUMBER () OVER () … Refer MSDN
http://msdn2.microsoft.com/en-us/library/ms186734.aspx for.

Answer / sanjay kumar dinda

Deletion of Rows depends upon the different condition...
Consider based on n columns the entire recordset is
treating a duplicate... We can delete the duplicate by
using following method...

Add one column and set uniue values to that column..

ALTER TABLE TABLE1 ADD ID INT IDENTITY(1,1)

COnsider T contains 40 columns and based on C1,C2,C3 we
have to find the duplicate and we have to delete the same...


Delete T1

FROM TABLE1 T1,TABLE1 T2
WHETE T1.C1=T2.C1 AND
T1.C2=T2.C2 AND
T1.C3=T2.C2
AND T1.ID>T2.ID


I think this will help....

Answer / vignesh chandrasekaran

select Distinct column name from Table Name

Answer / vineet dhamija

the best approach i came across in simple form too
just create a temporary table with the distinct values and
truncate this table than copy the values back and u r good to go
select distinct * into #temp from urtable
truncate table urtable
insert into urtable select * from #temp
drop table #temp

Answer / gerry

delete from my_table where my_primary_key in
(select a.primary_key from my_table a, my_table b
where not a.my_primary_key = b.my_primary_key
and [insert restriction that makes the 2 rows the same]
)

Explain about system stored procedure?

0 Answers  

How can we improve performance by using SQL Server profiler?

0 Answers   QuestPond,

Explain the characteristics of a transaction server for example atomicity, consistency, isolation, durability?

0 Answers  

you have a table with close to 100 million records recently, a huge amount of this data was updated now, various queries against this table have slowed down considerably what is the quickest option to remedy the situation? : Sql server administration

0 Answers  

What are joins in sql and what are the different types of joins?

0 Answers  

select names starts with a,b,c

8 Answers   Scope International,

How many partitions a clustered index has by default in sql server 2012?

0 Answers  

what is a schema in sql server 2005? Explain how to create a new schema in a database? : Sql server database administration

0 Answers  

How to execute stored procedure in select statement sql server?

0 Answers  

What are xml indexes?

0 Answers  

explain different types of backups avaialabe in sql server? : Sql server database administration

0 Answers  

what is the system function to get the current user's user id? : Sql server database administration

0 Answers