Duplicate Rows in an SQL Database

There is often a need to find rows that have duplicate values. You can use this query to find duplicate rows in an SQL database when they have duplicate value in a single column or multiple columns, depending on your need.

Here’s an example of a simple query:

SELECT firstname, COUNT(*) occurrences
FROM [users]
GROUP BY
	firstname
HAVING 
	COUNT(*) > 1

This will give you a simple list that shows you any duplicate firstname values in the users table and the count of how many duplicates there are.

So now you know that there are duplicates, how can you make use of this data? In order to do something with the data, you’ll need some more information, such as the primary key for the rows.

You can’t add that to the query columns since that would then make every row unique and they’d no longer show up in your resultset as duplicate. What you need to do is wrap this query into a subquery that then pulls these records out as a predicate.

You can read more about how the GROUP BY works on Microsoft’s site here:

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver16

  SELECT * FROM [users] WHERE firstname IN (
	SELECT firstname FROM (
		SELECT firstname, COUNT(*) occurrences
		FROM [users]
		GROUP BY
			firstname
		HAVING 
			COUNT(*) > 1) a
	)

Your original duplicate query is buried in there from lines 3 to 8. We have to alias that query in order to use it as a resultset. I simply called it ‘a’ for this example. The second inner SELECT is needed to knock off the count column in order for it to work in the WHERE IN clause of the outer SELECT statement. This now gives us a list of very actionable data.

Author: Jack Yasgar

Jack Yasgar has been developing software for various industries for two decades. Currently, he utilizes C#, JQuery, JavaScript, SQL Server with stored procedures and/or Entity Framework to produce MVC responsive web sites that converse to a service layer utilizing RESTful API in Web API 2.0 or Microsoft WCF web services. The infrastructure can be internal, shared or reside in Azure. Jack has designed dozens of relational databases that use the proper primary keys and foreign keys to allow for data integrity moving forward. While working in a Scrum/Agile environment, he is a firm believer that quality software comes from quality planning. Without getting caught up in analysis paralysis, it is still possible to achieve a level of design that allows an agile team to move forward quickly while keeping re-work to a minimum. Jack believes, “The key to long term software success is adhering to the SOLID design principles. Software written quickly, using wizards and other methods can impress the business sponsor / product owner for a short period of time. Once the honeymoon is over, the product owner will stay enamored when the team can implement changes quickly and fix bugs in minutes, not hours or days.” Jack has become certified by the Object Management Group as OCUP II (OMG Certified UML Professional) in addition to his certification as a Microsoft Certified Professional. The use of the Unified Modeling Language (UML) provides a visual guide to Use Cases and Activities that can guide the product owner in designing software that meets the end user needs. The software development teams then use the same drawings to create their Unit Tests to make sure that the software meets all those needs. The QA testing team can use the UML drawings as a guide to produce test cases. Once the software is in production, the UML drawings become a reference for business users and support staff to know what decisions are happening behind the scenes to guide their support efforts.

Leave a Reply

Your email address will not be published. Required fields are marked *