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
Thanks Mr.Pinal Dave
Hi Pinal,
I want to fetch the records from table1 having primary key ID but 1 column value depends on the condition and condition is : if ID is available in another table then column value should be “ColumnValue EXIST” if Not in another table then “ColumnValue NOT EXIST”.
Means I want to append some text to that column based on condition.
Thanks,
Deepak
I have got 1 Solution
select ID,case
when (select distinct 1 from table2 where table1.ID=table2.ID)=1
then value+’ EXIST’
else
value+’ NOT EXIST’
end
as status
from table1
any better solution would be appreciable..
Thanks,
Deepak
Thank you
thank you…
thnks it’s gud.:)
Yes! This is exactly what I needed a bit of help with! Perfect!
Thanks Deepak, I just added CTE to compare against
WITH
table1 (ID, value) AS
(SELECT 1 , ‘ID1’
union all SELECT 2, ‘ID3’),
table2 (ID, value, meta) AS
(SELECT 1, ‘ID1’, ‘?’
union all SELECT 3,’ID2′, ‘”‘)
SELECT ID,
CASE WHEN
(SELECT DISTINCT 1 FROM table2
WHERE table1.ID = table2.ID) = 1
THEN value + ‘EXIST’
ELSE value + ‘NOT EXIST’ END AS status
FROM table1
great job, so far the only working solution for me
thanks!
thanks
Hi,
If someOne has an idea… it’s will save my day…
I have a table with 2 columns : StartDateTime, Duration (Time)
How can i find conflicts : 2 rows on same time slot (not necesary starting or ending at same time but having common time) ?
Thanks
Hi sir,
I want to know how to get the max() of count()
Thanks and regards,
bhanu
Hi Bhanu,
You can get it by using CTE. I hope this will work for you.
with CTE
as
(
select count(*) as count_Rec from Table_Name group by Column_Name
)
select MAX(count_Rec) from CTE
Thanks,
Deepak
@bhanu – Can you provide sample DDL, some data and desired output?
Thanks a lot it really very helpful
Bimal – I am glad.
Hi Pinal,
Thanks for your great posts. I need a simple solution for, We need to identify duplicate in afield and mark second entry of duplicate as “Duplicate”
Output
——
IN00600010800 2TDS
IN00600010800 Duplicate
IN00600010801 TDS
Can you please help me out?
Thanks,
Aneesh
i have problem in which i have two different ID’s which has same mobile number now what to do is to keep both the ID’s and remove one mobile number i have two date columns like joined_date and Last_updated i have to update it based on last_updated
can you please help me on this
here is the sample data what i have and there are more than 5 different external_id’s which same mobile
mobile external_id join_date last_purchase
9216910814 100000003 2014-01-13 00:10:00 2015-01-27 00:02:00
9216910814 100000004 2014-01-27 00:11:00 2015-01-19 00:07:00
i need output like this mentioned below
mobile external_id join_date last_purchase
9216910814 100000003 2014-01-13 00:10:00 2015-01-27 00:02:00
100000004 2014-01-27 00:11:00 2015-01-19 00:07:00
Hi,
I need help in getting top 10 groups of records in sql server 2012. For example if I have 50 groups of 5 unique rows/records then I need a query to select top 5 groups (25 rows).
If I select top 5 rows from the table after applying group by clause, it gives me top 5 rows only and not the group.
Thanks mate!
Iam doing a e-commerce project , in that i need to get the count of all duplicate values of different products, can any one help me.
Thanks in advance.