86291

Find duplicate concat values

Question:

I'm trying to find each row with duplicates in a MySQL database.

1 alex smith 2 bob smith 3 alex smith

I want to return:

1 alex smith 3 alex smith

This code will find duplicates, but it doesn't list each row that is a duplicate.

SELECT *, CONCAT(`firstName`, ' ', `lastName`) as full_name, COUNT(*) d FROM users GROUP BY full_name HAVING d > 1;

The code below is what I'm trying to get each row that is a dupliate, but I get the error "#1054 - Unknown column 'full_name' in 'IN/ALL/ANY subquery'"

SELECT CONCAT(`firstName`, ' ', `lastName`) as full_name FROM users WHERE full_name IN ( SELECT CONCAT(`firstName`, ' ', `lastName`) as full_name FROM users GROUP BY full_name HAVING COUNT(full_name) > 1 )

Answer1:

You cannot use aliases in WHERE clauses; and you do not actually need the CONCAT <em>(it increases your computational costs in this case)</em>; instead you can compare "tuples".

SELECT * FROM users WHERE (`firstName`, `lastName`) IN ( SELECT `firstName`, `lastName` FROM users GROUP BY `firstName`, `lastName` HAVING COUNT(*) > 1 );

Recommend

  • Adding Secondary index on Cassandra indexes historical data?
  • yii cgridview sort not working
  • Why do attr_accessor virtual attributes not get outputted when you do record.inspect
  • Table row splits across two pages (print media)
  • how do you “Press ENTER to continue, anything else to quit” in C++
  • Relay Error when deleting: RelayMutationQuery: Invalid field name on fat query
  • finding symmetric difference/unique elements in multiple arrays in javascript
  • Button inside Update Panel is not triggered, in asp.net
  • How do I make WIF use RSA15 when encrypting data using a certificate?
  • Multi-line JSON read using Apache PIG
  • Gruntfile.js - Throwing error 'Recursive process.nextTick detected\"
  • sql if statement checking for null parameter
  • Passing Props from State to Child
  • JSF Cannot format given Object as a Date
  • LINQ to Entities does not recognize the method 'System.Collections.Generic.Dictionary`2[System.
  • Merge list of objects into consistent list based on common matching attribute in Python
  • Insert records if not exist SQL Server 2005
  • Problem in concatenation of objects in javascript
  • Get current user from inside the model in Sails
  • Strong vs Weak entities MYSQL
  • Unique Permutations - with exceptions
  • Cloud Code function running twice
  • Bad request using file_get_contents for PUT request in PHP
  • Disable Enter in editText android
  • Django: Count of Group Elements
  • Dynamically accessing properties of knockoutjs observable array
  • Sending data from AppleScript to FileMaker records
  • MySQL WHERE-condition in procedure ignored
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • Codeigniter doesn't let me update entry, because some fields must be unique
  • How do you join a server to an Active Directory (domain)?
  • How to CLICK on IE download dialog box i.e.(Open, Save, Save As…)
  • Turn off referential integrity in Derby? is it possible?
  • How does Linux kernel interrupt the application?
  • How can I remove ASP.NET Designer.cs files?
  • Add sale price programmatically to product variations
  • Unable to use reactive element in my shiny app
  • java string with new operator and a literal
  • How do I use LINQ to get all the Items that have a particular SubItem?