Please give me queries for the following
1. To get the count of duplicate records.
2. A query to delete the duplicate records.
- 3 Answers
- 6102 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
In one interview...interviewer ask me question pleas tell me sql server architecture.. can any body tell me the sql server architecture with digram
New concepts of sql server 2005 use in your project.
What does indexation mean?
If you are given access to a SQL Server, how do you find if the SQL Instance is a named instance or a default instance?
What does the on update no action do?
How to sort query output in descending order in ms sql server?
How can we remove orphan records from a table?
I have triggers,views,functions,stored Procedures for a table. When I am dropping that table which objects are deleted?
What is normalization according to you and explain its different levels?
How can i Relate Tables in SSIS
What are audit control procedures?
Do you know what is a with(nolock)?
Explain user defined functions?
what is difference between view and Dip.
What number sorts of privileges are accessible in sql?
