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

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

14 Answers
22166 Views
I also Faced
E-Mail Answers

Answer Posted / 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

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

What is spid in sql server profiler?

1093

what number files will a information contain in SQL Server? How many forms of information files exist in SQL Server? How many of those files can exist for a single database?

1052

How to write a query with a left outer join in ms sql server?

968

explain extended properties

982

What does executeupdate return?

969

What is difference between unique and primary key?

899

What factors you will consider calculating the storage requirement for that view?

939

How do I find query history in sql server?

935

do you know how to configure db2 side of the application? : Sql server database administration

1039

Do you think BCNF is better than 2NF & 3NF? Why?

1272

Detail about the hardware which is supported by SQL server?

1057

Explain having clause?

957

How many replicas are maintained for each SQL Azure database?

142

what is a self join? : Sql server database administration

1030

What is difference between primary key and foreign key?

894