1. use group by clause with the field on which you want to check for dupl

Please give me queries for the following
1. To get the count of duplicate records.
2. A query to delete the duplicate records.

Question Posted / srikanth

3 Answers
6775 Views
247Customer, I also Faced
E-Mail Answers

Answer Posted / srikanth

1. use group by clause with the field on which you want to
check for duplicates

Ex: select salary,count (*) from employee group by salary
having count (*)>1

2. Use the below query to delete the duplicate records (not
the original ones)

SET ROWCOUNT 1
DELETE yourtable
FROM yourtable a
WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 =
a.name1 AND b.age1 = a.age1) > 1
WHILE @@rowcount > 0
DELETE yourtable
FROM yourtable a
WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 =
a.name1 AND b.age1 = a.age1) > 1
SET ROWCOUNT 0

Is This Answer Correct ?

5 Yes

0 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

Explain in brief about Microsoft SQL server?

1085

How to create a view and a stored procedure in ms sql server using "create view/procedure" statements?

1004

Describe in brief system database.

1005

What are indexes in ms sql server?

1050

What is a unique key constraint?

1022

What happens if ntwdblib.dll is missing on your machine?

1138

What is the difference between Clustered and Non-Clustered Index?

1051

Explain the dbcc pintable command when would you use it?

905

How do you Implement SSIS Packages in your Project?

1919

What is the use of tempdb? What values does it hold?

1062

Define cross join in sql server joins?

1016

What is the process of normalising?

993

What is format parameter in ssrs?

146

what is an extended stored procedure? Can you instantiate a com object by using t-sql? : Sql server database administration

1013

Can you give me some DBCC command options?(Database consistency check) - DBCC CHECKDB - Ensures that tables in the db and the indexes are correctly linked.and DBCC CHECKALLOC - To check that all pages in a db are correctly allocated. DBCC SQLPERF - It gives report on current usage of transaction log in percentage. DBCC CHECKFILEGROUP - Checks all tables file group for any damage.

2510