Sign Up Login


  1. Categories >> Software >> Databases >> SQL Server
  2. Suggest New Category

Delete duplicate rows from a table without primary key by
using a single query
Table Employee
empname salary
A 200
B 300
A 200
C 400
D 500
D 500

Output should be

A 200
B 300
C 400
D 500

Question Posted / ayanb1302@yahoo.com

Answers were Sorted based on User's Feedback



Delete duplicate rows from a table without primary key by using a single query Table Employee em..

Answer / swati tripathi

declare myCursor cursor for
select empid
from employee
group by empid
having count(*) > 1

declare @EmpId int


OPEN myCursor;
FETCH NEXT FROM myCursor INTO @EmpId
WHILE @@FETCH_STATUS = 0
BEGIN

delete top(select count(*)-1 from employee where
empid=@EmpId) from employee where empid=@EmpId

FETCH NEXT FROM myCursor INTO @EmpId
END
close myCursor

deallocate myCursor

Is This Answer Correct ?    11 Yes 0 No

Delete duplicate rows from a table without primary key by using a single query Table Employee em..

Answer / sumathy

Execute the following query for each duplicate value.

1.Delete duplicate of empname='A'

delete top(select count(*)-1 from employee where
empname='A') from employee where empname='A'

2.Delete duplicate of empname='D'

delete top(select count(*)-1 from employee where
empname='D') from employee where empname='D'

Is This Answer Correct ?    9 Yes 2 No

Delete duplicate rows from a table without primary key by using a single query Table Employee em..

Answer / navneet sharma

select distinct * from tablename

Is This Answer Correct ?    4 Yes 1 No

Delete duplicate rows from a table without primary key by using a single query Table Employee em..

Answer / honey

with myCTE as(
select row_number() over( partition by empname order by
empname) as myCount from Employee
)delete from myCTE where myCount >1

Is This Answer Correct ?    3 Yes 0 No

Delete duplicate rows from a table without primary key by using a single query Table Employee em..

Answer / sunil

set rowcount 1 -- set row count 1

delete a from Employee a
where (select count(*) from Employee e where e.empname =
a.empname) > 1

while @@rowcount > 0
begin
delete a from Employee a
where (select count(*) from Employee e where e.empname =
a.empname) > 1
end
set rowcount 0

Is This Answer Correct ?    2 Yes 0 No

Delete duplicate rows from a table without primary key by using a single query Table Employee em..

Answer / pradeep

DECLARE @TEMP_TABLE TABLE (EMPNAME VARCHAR(10),SALARY
VARCHAR(10))

INSERT INTO @TEMP_TABLE (EMPNAME ,SALARY )
SELECT 'A','200' UNION ALL
SELECT 'B','300' UNION ALL
SELECT 'C','400' UNION ALL
SELECT 'A','200' UNION ALL
SELECT 'A','200' UNION ALL
SELECT 'D','500' UNION ALL
SELECT 'D','500' UNION ALL
SELECT 'B','300'

/* TABLE RAW DATA */
SELECT * FROM @TEMP_TABLE

SELECT * FROM @TEMP_TABLE AS A
WHERE (SELECT COUNT(*) FROM @TEMP_TABLE AS B WHERE
A.EMPNAME=B.EMPNAME AND A.SALARY=B.SALARY) > 1

/* OUTPUT SHOULD BE */
SELECT DISTINCT * FROM @TEMP_TABLE

/* DELETE DUPLICATE/TRIPLICATE.... RECORDS */
SET ROWCOUNT 1

DELETE @TEMP_TABLE FROM @TEMP_TABLE AS A
WHERE (SELECT COUNT(*) FROM @TEMP_TABLE AS B WHERE
A.EMPNAME=B.EMPNAME AND A.SALARY=B.SALARY) > 1
WHILE @@ROWCOUNT > 0
DELETE @TEMP_TABLE FROM @TEMP_TABLE AS A
WHERE (SELECT COUNT(*) FROM @TEMP_TABLE AS B WHERE
A.EMPNAME=B.EMPNAME AND A.SALARY=B.SALARY) > 1

SET ROWCOUNT 0
/**********************************************/
/* EXPECTED OUTPUT TABLE */
SELECT * FROM @TEMP_TABLE

Is This Answer Correct ?    1 Yes 0 No

Delete duplicate rows from a table without primary key by using a single query Table Employee em..

Answer / shankaranarayanan v

while exists(select count(*) from employee group by empname having count(*)>1)
begin

delete top(1) from employee where empname in
(
select min(empname) as deletedname
from employee
group by empname
having count(*)>1
)

end

Is This Answer Correct ?    1 Yes 0 No

Delete duplicate rows from a table without primary key by using a single query Table Employee em..

Answer / devender kumar

This query is for sql server 2005 and higher version of sql
server. It will not run on older versions.



with myCTE as(
select row_number() over( partition by empname order by
empname) as myCount from Employee
)delete from myCTE where myCount >3

Is This Answer Correct ?    1 Yes 1 No

Delete duplicate rows from a table without primary key by using a single query Table Employee em..

Answer / gaurav sharma

SELECT DISTINCT * INTO Employee1 FROM Employee
DROP Table Employee
sp_rename 'Employee1','Employee'

Is This Answer Correct ?    0 Yes 1 No

Delete duplicate rows from a table without primary key by using a single query Table Employee em..

Answer / samba shiva reddy . m

delclare @temp(empname varchar(50),sal int)
select * into @temp
FROM employee
GROUP BY empname, salary
HAVING count(*) > 1
delete from employee
select * into employee from @temp as temp

Is This Answer Correct ?    0 Yes 1 No

Post New Answer

More SQL Server Interview Questions

How to check table values in sql server?

0 Answers  

What is key attribute?

0 Answers  

What is the minimum recommended amount of ram for sql server 2012 enterprise?

0 Answers  

What are the differences between lost updates and uncommitted dependencies?

0 Answers  

I Have Employee table having column name as ID,SALARY how to get second max salary from employee table with id ex ID SALARY 1 20000 7 37000 2 5000

17 Answers   HCL, IBM,

What are the different sql server versions?

0 Answers  

how many non clustered index in sql server 2008,2010,2012

2 Answers   Accenture,

how to do partition in sqlserver

0 Answers   TCS,

write down the code for "how we delete a table without using of Drop command " ?

7 Answers   CarrizalSoft Technologies, Sonata,

What is user defined stored procedures?

0 Answers  

1.Describe way(s) to tune the SQL table in order to optimize performance. 2. Explain SQL Injection and how can you prevent them?

1 Answers   Techno Solutions,

When I delete any data from a table, does the sql server reduce the size of that table?

0 Answers  

For more SQL Server Interview Questions Click Here
Categories