In my previous article SQL SERVER – Delete Duplicate Records – Rows, we have seen how we can delete all the duplicate records in one simple query. In this article we will see how to find count of all the duplicate records in the table. Following query demonstrates usage of GROUP BY, HAVING, ORDER BY in one query and returns the results with duplicate column and its count in descending order.
SELECT YourColumn, COUNT(*) TotalCount
FROM YourTable
GROUPÂ BY YourColumn
HAVING COUNT(*)Â > 1
ORDERÂ BY COUNT(*) DESC
Watch the view to see the above concept in action:
[youtube=http://www.youtube.com/watch?v=ioDJ0xVOHDY]Reference : Pinal Dave (https://blog.sqlauthority.com) , SQL SERVER – Delete Duplicate Records – Rows
187 Comments. Leave new
its a good solution so far …
thanks
Good
Great solution, many thanks for this.
Dear Pinal,
I visited your blog so manytimes and get the solution. Thank you so much for keep updating your blog.
I have problem and i wanted you to give some time to resolve and given me a solution.
I have one table containing the records like this
Movie ID Movie Name New Name
1 Spider Man Spider Man
2 Spider Man 2 Spider Man
3 Spider Man 3 Spider Man
4 Spider Man UK Spider Man
5 Spider Man USA Spider Man
6 New Spider Man Spider Man
7 Spider Man Black Spider Man
8 Spider Man Part 1 Spider Man
9 Spider Man Part 2 Spider Man
10 Spider Man I Spider Man
11 Spider Man III Spider Man
12 Spider Man Part II Spider Man
My manufacturer send me the data in this format and i have to allocate there new name to do some comparison
I wanted to make this process automatic.
what i mean is i need a query which will give me a duplicate records and also the suggestion for the new name.
I am fully confident that you will help me out from this problem.
Looking forward
Rana
Hi Pinal.
I have same problem as Rana.
Please try to solve it. I really need a solution.
Thanks in advance.
select day, count(*) from test GROUP BY day;
USE IT
select day, count(*) from test GROUP BY day;
EASY AND LESS COMPLICATED ANS
Dear Pinal,
how to find nth highest salary without top 1 functions
Dear pinal,
any luck with the solutions.
Please find some time to write custom solution.
I hope you understand my problem and how to resolve it.
Good Luck.
Looking forward on this weekend.
Rana
Dude rana, why dont you find your own solution instead of scamming people who actually know how to write sql.
Dear Pinal and Vistor Dominator,
I wrote here because i was wondering that you must have done something before so i can use that.
but no worries i have spent some time and get the solutions.
Few algo i wrote.
some counts and its really a wonderful solutions for even cleanign the data.
I will be posting this in this weekend.
I need to explain line by line otherwise people might not udnerstand.
thanks anyway.
Great tip; exactly what I was looking for. Thanks for posting it.
I want the show a result that choose the line that has the lowest price
What do you suggest
Table
Item Code Price
Spider $10.00
Spider $9.00
Ant $5.00
Ant $4.00
The result I want to see would be
Spider $9.00
And $4.00
Thanks
select spider,min(price) as price from table
group by spider
Thanks your solution was exactly what I was looking for. I had a bunch of ID’s with a different times of a code, that I need to know how many occurrences for each individual. Your code gave all that, and allow me to get the data to program accordingly.
It is great article. It real useful to me for quick result. Thank you, keep post this kind of articles.
Thank you, your solution worth 5 marks for my coursework. ;-)
THis works for me very nice!
Thnx!
Hi Pinal,
Thanks for the great info. I couldn’t believe how easy it is to accomplish what I was trying to do. I took what you wrote and modified it a bit since I only needed to know how many of each duplicate I had.
Thanks realy imagine
For 11. Alex, you could do this perhaps:
SELECT
ITEM_CODE,
MIN(PRICE) AS PRICE
FROM TABLE
GROUP BY ITEM_CODE
ORDER BY ITEM_CODE
Hi Pinal,
This is really too good solution to find and delete duplicate records from database table.
Thanks ,
Pravin
Hi Pinal,
One more soultion to Delete completely similar record from database table
We can play with row Id of the table row to delete a completely similar records , keeping one avilable using sysobjects in sql server 2005.
Thanks,
Pravin.
Thanks a lot.
select sales6.Sou_Enqu,(count(sales5.project_reg)),(count(sales5.project_EMRB))from sales6,sales5 where sales5.sales_no=sales6.sales_no and sales5.project_reg =’Regent Park’and sales5.project_EMRB=’ECB’ Group by sales6.Sou_Enqu;
this is my query i want count 10 field value in that query both field count value is displaed same.
source regent park ecb eck
newpeper 10 20 30
email 20 10 20
like that format
plz help me soon
tell me solution