Answer / kishor kumar

This can be solved by 2 Ways

One way

Create unique index on this Table with ignore duplicate row

2nd way.

1st transfer the data to a temp table bu using Distinct
clause then truncate the EMP table and then transfor the
data from temp table to EMP Table.

select distinct (column names) in to #temp from EMP

Truncate table EMP

INSERT INTO EMP

SELECT * FROM #temp

Answer / soorai ganesh

Hey dude, If u use SQLSERVER 2005 This will help u.


;WITH tmpEmp AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY EmpID ORDER BY EmpID)
AS RowNumber, * FROM EMP
)

DELETE FROM tmpEmp WHERE RowNumber> 1

Answer / siddharth

DECLARE @int as int
DECLARE @cnt as int
WHILE EXISTS (SELECt top 1 EmpId from Emp group by EmpId having count(EmpId)>1 order by EmpId)
Begin
SELECT top 1 @int=EmpId,@cnt=Count(EmpId) from Emp group by EmpId having count(EmpId)>1 order by EmpId
Delete top (@cnt-1) from Emp where EmpId = @int
End

Select EmpId,Ename,Sal,DeptId from Emp order by EmId

Answer / shankar

First Of All Create Table
create table EMP(empid int, ename varchar(10),sal int,
deptid int)

insert into EMP values(11, 'Ram', 10000, 10)
insert into EMP values(11, 'Ram',10000,10)
insert into EMP values(22, 'Raj', 20000, 20)
insert into EMP values(22, 'Raj', 20000, 20)
insert into EMP values(33, 'Anil', 15000, 30)
insert into EMP values(33, 'Anil', 15000, 30)
insert into EMP values(44,'bbb',11111,40)
--Now Run This query It works
Declare @empID int, @RecCount int,@Name varchar (10),@Sal
int, @deptid int
SET @RecCount = 0
Declare MyCur Cursor for
Select empid,ename Sal,DeptID,Count(ID)-1 as Records from
EMP Group BY empid,ename Sal,DeptID having Count(empid)>1
Open MyCur
Fetch FROM MyCur INTO @empID,@Name,@sal,@DeptID,@RecCount

WHILE @@FETCH_STATUS = 0
BEGIN
SET ROWCOUNT @RecCount

DELETE from EMP where empID= @empID
SET @empID= 0
SET @Name = ''
SET @Sal= 0
SET @DeptID = 0
SET @RecCount = 0
SET ROWCOUNT 0
Fetch FROM MyCur INTO @empID,@Name,@sal,@DeptID,@RecCount
END

Close MyCur
DEallocate MyCur

Answer / aashish lad

-- Here Temp1 is Temporary Table So it will take All records
-- From Mytable With RowNumbar column
-- We can Delete the Record from


SELECT ROW_NUMBER() OVER(PARTITION BY empid ORDER BY empid)
AS RowNumber, * into #temp1 FROM mytable

DELETE FROM #temp1 WHERE RowNumber> 1

INSERT INTO mytable

SELECT * FROM #temp

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 / guest

WITH T1 AS (
SELECT ROW_NUMBER () OVER ( PARTITION BY EmpId ORDER BY
EmpId) AS RNUM,EmpId FROM EMP)
delete
FROM T1 WHERE RNUM > 1

Answer / vijay

I have tried this query in my PC.Very simply I was able to
remove all duplicate rows.
Please try below query it will work out.

SELECT DISTINCT*FROM EMP(TableName);

You will get below table
EmpId Ename Sal DeptId
11 Ram 10000 10
22 Raj 20000 20
33 Anil 15000 30


Suppose if you have different ENAME but you want only
selected ENAME without duplicate records.
Where We can use below query.

SELECT DISTINCT*FROM EMP WHERE ENAME IN('RAM','RAJ','ANIL');

Please correct me if I am wrong.

Answer / vikas kant

select Distinct * into #Temp_Temp1 from Emp
truncate table Emp
insert into Emp select * from #Temp_Temp1
select * from Emp

Answer / mohan

create table #temp(empid int, ename varchar(10),sal int,
deptid int)

insert into #temp values(11, 'Ram', 10000, 10)
insert into #temp values(11, 'Ram',10000,10)
insert into #temp values(22, 'Raj', 20000, 20)
insert into #temp values(22, 'Raj', 20000, 20)
insert into #temp values(33, 'Anil', 15000, 30)
insert into #temp values(33, 'Anil', 15000, 30)
insert into #temp values(44,'bbb',11111,40)

select * from #temp

set rowcount 1
delete from #temp where empid in(select empid from #temp
group by empid having count(*)>1)
while @@rowcount>0
begin

delete from #temp where empid in(select empid from #temp
group by empid having count(*)>1)
end
set rowcount 0

select * from #temp

What is the maximum number of instances in 32 bit and 64 bit sql server 2012?

0 Answers  

What does null mean?

0 Answers  

What are the transaction properties?

0 Answers  

What is used to replicate sessions between instances in coldfusion clusters?

0 Answers   HCL,

How to replace null values in expressions using isnull()?

0 Answers  

How to get a list of columns in a view using the "sp_columns" stored procedure?

0 Answers  

Distinguish between nested subquery and correlated subquery?

0 Answers  

Create a dts package to produce a text file using the ‘update statistics’ command for the tables in a database with obsolete statistics.

0 Answers  

What is a data source or ds? : sql server analysis services, ssas

0 Answers  

which database is best to use in oracle and sql server? explain reasons?

5 Answers   TCS,

What is sqlcmd?

0 Answers  

when u import an excel file into sql if suppose one column has got a date field with system time attached to it what data type u will use in sql to import it in a table? Note: a condition is the excel file has no primary key defined to a column...also u r importing bulk data into sql

1 Answers