Please give me queries for the following
1. To get the count of duplicate records.
2. A query to delete the duplicate records.
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
What is triggers and stored procedures?
What are commit and rollback in sql?
What are the purposes and advantages stored procedure?
How to use "if ... Else if ... Else ..." Statement structures in ms sql server?
What are the new features introduced in SQL Server 2000 (or the latest release of SQL Server at the time of your interview)? What changed between the previous version of SQL Server and the current version?
what r steps to we need to fallow b4 kill the process?
how to delete duplicate rows in sql server2005
7 Answers Cisco, CTS, HCL, IBM,
Explain sql delete command?
Which table keeps the locking information?
Is it possible to update the views? If yes, how, if not, why?
What are different types of data sources?
Difference between report and query parameter. Why do we need different type of parameter?