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
This one is a great help however I’ve tried retrieving other fields besides the one I’m counting with duplicate record. How do I do that? This is my script. I can’t seem to include the claim_no, lastname and firstname field.
SELECT bank_acct, COUNT(*)
FROM july09_master_copy
WHERE cl_status = ‘z’
AND rid_ind = ‘r’
GROUP BY bank_acct
HAVING COUNT(*) >1
ORDER BY COUNT(*)
SELECT t1.bank_acct,t1.claim_no,t1.lastname from july09_master_copy as t1 inner join
(
SELECT bank_acct, COUNT(*)
FROM july09_master_copy
WHERE cl_status = ‘z’
AND rid_ind = ‘r’
GROUP BY bank_acct
HAVING COUNT(*) >1
) as t2 on t1.bank_acct=t2.bank_acct
hi all
can you help me, I want to find transaction with the same minutes on the table transactions
example case : I have 2 user which has 3 time transaction on the same minutes of the day on my Transaction table
can you give me the example script please …send it my email : ifi.lutfi@pln.co.id
Thanks …
Hi lutfi
may be this will help you
select user_name,transaction_id, transaction_time from transactions where transaction_time in (select transaction_time from transactions group by transaction_time having count(transaction_time) > 1)
Good solution, many thanks for this.
Thank you Pinal & udai,
great help in increasing my sql knowledge :).
My Statement currently looks like this:
SELECT
FIELD15
FROM CUSTOM
HAVING COUNT (*)>1
GROUP BY FIELD15
However I’m wanting to add another column in this statement so I’m able to see the account numbers associated with Field15
Please help!
Thank you!
@Nicole
So, you want account numbers for any group of Field15 that has more than one member?
SELECT Field15, Account FROM Custom WHERE EXISTS(SELECT * FROM Custom B WHERE B.Field15 = Custom.Field15 GROUP BY Field15 HAVING COUNT(*) > 1);
Brian-
Thank you so much for helping me! That worked, but didn’t provide the information I’m needing.
I’ll explain what this report is currently doing and what I need it to do. It currently pulls all duplicated secondary account numbers (field15) off of the primary account (account) from the report (custom). What I need it to do is provide the primary account number to where the secondary account number is being duplicated (for example I’ll have 2 different primary accounts numbers but the same secondary account number associated with the primary accounts) so I know what account how the error. It’s harder to explain something when somebody has no clue what your talking about :), I really appreciate the help!
Does that make sense?
@Nicole
I don’t understand
The query i posted will grab any information wwhere Field15 is duplicated. If you want another COLUMN, why not just add it:
SELECT Primary_Account, Field15, Account FROM Custom WHERE EXISTS(SELECT * FROM Custom B WHERE B.Field15 = Custom.Field15 GROUP BY Field15 HAVING COUNT(*) > 1);
Perhaps if you posted the TABLE structure, a small amount of data (3 records should be fine, 1 duplicated (making a total of 2), one not) and what results you expect.
I was having a dedede moment, it works! Thank you!
Great solution………………………
Thanks
Hello Mr. SQL Sir,
I want to find alternate records in sql server.
Can u please suggest me a way for this.
Hi Manjeet,
I am not clear about your requirement of alternate records.
Regards,
Pinal Dave
The requirement is i want to retrieve records from alternate rows like 1st,3rd,5th,7th and so on….. from a table.
How can i do it???
I tried googling on it but that is showing the use of mod() function which is not supported in sql server 2005.
Hello Manjeet,
Following is the script to get alternate TableCol1 rows from table TableTest:
WITH CTE (RN, TableCol1)
AS
(SELECT ROW_NUMBER() OVER (ORDER BY TableCol1), TableCol1 FROM TableTest)
SELECT TableCol1 FROM CTE WHERE RN%2 = 1
Regards,
Pinal Dave
Hi Pinal bro,
i have a problem
i want to delete duplicate rows
except one row on duplicate rows
that will not be deleted.
i mean to say i have record like this :
ID : 1 Name : fawad
ID : 2 Name : ashfaq (this should not be deleted)
ID : 2 Name : Pinal (this should be deleted)
ID : 2 Name : Dave (this should be deleted)
ID : 3 Name : Manooj
please help me
Thanks
Fawad ashfaq
@Fawad
look at the answer Pinal gave to Manjeet, and change: RN%2 = 1 to RN > 1
Hello Fawad,
Use the ROW_NUMBER function as in below example:
WITH xTab AS
(
SELECT Column1, Column2,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS [Count]
FROM MyTable
)
DELETE FROM xTab WHERE [Count] > 1
Regards,
Pinal Dave
kalyan.
hello Pinal,
I want to retrieve the alternate records in a table.
Regards
kalyan
Hello Kalyan,
Alternate record on which column value? By position there is no alternate record. You can use the ROW_NUMBER function and modulo (%) operator to get alternate records.
Regards,
Pinal Dave
Hi pinal,
i have a question
if i want to retrieve the complete record(all colums values)?
i don’t know how to do this, as i can’t give all the column names in select clause, if i give so i have give the column names in GROUP BY clause which doe’s not give me the desired result,,
Waiting for your reply,
THANK YOU PINAL
@Shobha Reddy
Please give an example of the record and the desired results.
select t1.* from table as t1 inner join
(
your_query_that_finds_duplicates
) as t2 on t1.keycol=t2.keycol