Please give me queries for the following 1. To get the count of duplicate recor

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
6636 Views
247Customer, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

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

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

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

Answer / harshad lakkad (bapunagar part

For Count Duplicate---
SELECT YourColumn, COUNT(*) TotalCount
FROM YourTable
GROUP BY YourColumn
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
For delete Duplicate---
DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2,
DuplicateColumn2)

Is This Answer Correct ?

4 Yes

1 No

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

Answer / meher

1. To get the duplicate records.
Use group by clause to the column on which you want to
check the duplicates records.
syntax:
select column_name, count(*) from table_name group by
column_name having count(*) >1

2. To delete the duplicate records.
syntax:
delete from table_name where rowid in (select max(rowid)
from table_name group by column_name having count(*)>1 )

This will work.
Please let me know in case of any issues.

Is This Answer Correct ?

2 Yes

2 No

Post New Answer