adspace


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

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

How can I check that whether automatic statistic update is enabled or not?

1113

What is the difference between upgrade and migration in sql server?

1185

How to enter binary string literals in ms sql server?

1246

Equi join and non equi join is possible with sql server?

1134

What are the different subsets of sql?

1654

What is a view in sql?

1043

What is subquery? Explain the properties of a subquery?

1063

How can you append an identity column to a temporary table?

1071

Explain system functions or built-in functions? What are different types of system functions?

1067

Does view occupy space?

1022

What is sql server query analyzer?

1131

What are the pros and cons of putting a scalar function in a queries select list or in the where clause?

1300

Disadvantages of the indexes?

1225

Is there any performance difference between if exists (select null from table) and if exists (select 1 from table)?

1024

Why use identity in sql server?

1202