Skip to content
Fix Code Error

Find duplicate records in MySQL

March 13, 2021 by Code Error
Posted By: Anonymous

I want to pull out duplicate records in a MySQL Database. This can be done with:

SELECT address, count(id) as cnt FROM list
GROUP BY address HAVING cnt > 1

Which results in:

100 MAIN ST    2

I would like to pull it so that it shows each row that is a duplicate. Something like:

JIM    JONES    100 MAIN ST
JOHN   SMITH    100 MAIN ST

Any thoughts on how this can be done? I’m trying to avoid doing the first one then looking up the duplicates with a second query in the code.

Solution

The key is to rewrite this query so that it can be used as a subquery.

SELECT firstname, 
   lastname, 
   list.address 
FROM list
   INNER JOIN (SELECT address
               FROM   list
               GROUP  BY address
               HAVING COUNT(id) > 1) dup
           ON list.address = dup.address;
Answered By: Anonymous

Related Articles

  • How to remove MySQL completely with config and…
  • SQL query return data from multiple tables
  • Exception trying to get folder in MailKit, but not…
  • MS Access SQL query - Count records until value is met
  • How to create a column with a count of rows between…
  • How to set the 'selected option' of a select…
  • COUNT(*) vs. COUNT(1) vs. COUNT(pk): which is better?
  • How to return result of a SELECT inside a function…
  • Apply a EWMA rolling window function in Pandas but…
  • Database development mistakes made by application developers
  • mongodb group values by multiple fields
  • Should MySQL have its timezone set to UTC?
  • Grouping and concatening values in Pandas dataframes
  • How do SO_REUSEADDR and SO_REUSEPORT differ?
  • Combining items using XSLT Transform
  • Switch case with conditions
  • Reduce array according to grouping condition in reactjs
  • Improve INSERT-per-second performance of SQLite
  • How do I count unique visitors to my site?
  • Bootstrap Card - change width
  • How to Update Database from Assets Folder in App
  • (sml) I want to count the number of corresponding…
  • Trouble with qsort with key/value structs in a btree
  • I need to extract working hour breaks out of a Time…
  • How to return a custom object from a Spring Data JPA…
  • How to iterate through property names of Javascript object?
  • Start redis-server with config file
  • Why is Ember throwing "Uncaught Error: Assertion…
  • labeling rows in dataframe, based on dynamic conditions
  • LINQ with groupby and count
  • How to filter a RecyclerView with a SearchView
  • How can I initialize a MySQL database with schema in…
  • Is this request generated by EF Core buggy or is it my code?
  • node.js TypeError: path must be absolute or specify…
  • Text File Parsing and convert to JSON?
  • Numbering rows in pandas dataframe (with condition)
  • MySQL: Insert record if not exists in table
  • How can I append a query parameter to an existing URL?
  • MySQL "Group By" and "Order By"
  • The function "org.apache.jena.rdf.model.hasProperty"…
  • From inside of a Docker container, how do I connect…
  • Fix top buttons on scroll of list below
  • Calculate a count of groupby rows that occur within…
  • Spark EMR job jackson error -…
  • Entity Framework LINQ complex query - combine…
  • "Large data" workflows using pandas
  • Form field border-radius is not working only on the…
  • how to determine which bar was clicked on a chart js
  • Java Array, Finding Duplicates
  • Filter by field on relationship in SQLAlchemy
  • LINQ to SQL using GROUP BY and COUNT(DISTINCT)
  • What are database normal forms and can you give examples?
  • error: 'Can't connect to local MySQL server through…
  • IIS URL Rewrite and Web.config
  • How to get the employees with their managers
  • Best way to select random rows PostgreSQL
  • phpMyAdmin on MySQL 8.0
  • Remix error The transaction has been reverted to the…
  • Proper .htaccess config for Next.js SSG
  • ImportError: No module named dateutil.parser
  • HashMap with multiple values under the same key
  • ember-data: Loading hasMany association on demand
  • How to export data to an excel file using PHPExcel
  • Why does C++ code for testing the Collatz conjecture…
  • I want to create a SQLite database like in the…
  • What does an exclamation mark mean in the Swift language?
  • How can I fix MySQL error #1064?
  • MySQL select with CONCAT condition
  • Get top n records for each group of grouped results
  • Geofire query events not firing
  • Why cat does not work with parameter -0 in xargs?
  • Howto: Clean a mysql InnoDB storage engine?
  • Programmatically generate url from path and params
  • Replacing a 32-bit loop counter with 64-bit…
  • SFMC - Add Group Identifier in Automation Studio…
  • Replace duplicates in matrix
  • ERROR 1698 (28000): Access denied for user…
  • Select Tag Helper in ASP.NET Core MVC
  • Backbone.js Routers confusion (pushState: true,…
  • How would I be able to multiple select and pass data…
  • MySQL - SELECT WHERE field IN (subquery) - Extremely…
  • Simple Digit Recognition OCR in OpenCV-Python
  • Excel: Generating Months Data from Start and End Date
  • Best practice multi language website
  • VueJs doesn't update when binding passed props to data
  • MySQL order by before group by
  • "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"
  • Is there a way to join two queries in SQL each with…
  • html2canvas not working inside a php loop
  • Polymer 1.0 'array-style' path accessors,…
  • What is a non-capturing group in regular expressions?
  • MySQL database is not receiving any data in PHP
  • Make Iframe to fit 100% of container's remaining height
  • data.table vs dplyr: can one do something well the…
  • Vuetify / autocomplete : styling group
  • Compare two List objects for equality, ignoring order
  • How do I include certain conditions in SQL Count
  • Aurelia - repeat.for json
  • (SETF L (FLATTEN L)) should be a lambda expression error
  • Are PDO prepared statements sufficient to prevent…

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.

Post navigation

Previous Post:

Return multiple values to a method caller

Next Post:

Smooth scroll to div id jQuery

Leave a Reply Cancel reply

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

.net ajax android angular arrays aurelia backbone.js bash c++ css dataframe ember-data ember.js excel git html ios java javascript jquery json laravel linux list mysql next.js node.js pandas php polymer polymer-1.0 python python-3.x r reactjs regex sql sql-server string svelte typescript vue-component vue.js vuejs2 vuetify.js

  • you shouldn’t need to use z-index
  • No column in target database, but getting “The schema update is terminating because data loss might occur”
  • Angular – expected call-signature: ‘changePassword’ to have a typedeftslint(typedef)
  • trying to implement NativeAdFactory imports deprecated method by default in flutter java project
  • What should I use to get an attribute out of my foreign table in Laravel?
© 2022 Fix Code Error