Solved

MySQL: Get All Duplicates (not grouped)

Posted on 2016-09-25
3
63 Views
Last Modified: 2016-09-25
Using MySQL, how can I get a list of ALL items that have a duplicate `NumericID`?

In this example, it only shows the first matching duplicate.

http://sqlfiddle.com/#!9/ccb53/13/0
CREATE TABLE `fruits` (
  `Name` varchar(255) NULL,
  `x` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `fruits` VALUES 
('Peach','269'),
('Pear','269e'),
('Mango','67'),
('Orange','954'),
('Raspberry','75'),
('Cherry','93'),
('Prune','198'),
('Grape','73'),
('Lemon','954'),
('Lime','954'),
('Strawberry','75'),
('Aprocot','48');

SELECT `Name`, TRIM(TRAILING 'e' FROM x) AS `NumericID`
FROM fruits 
GROUP BY NumericID
having count(*) >= 2

Open in new window

For example, the query above returns this:      
Peach
Raspberry
Orange

I want it to return:

Peach
Pear
Orange
Lemon
Lime
Strawberry
Raspberry
Orange
0
Comment
Question by:skij
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
3 Comments
 
LVL 35

Expert Comment

by:Terry Woods
ID: 41815100
SELECT `Name`, TRIM(TRAILING 'e' FROM x) AS `NumericID`
FROM fruits 
WHERE NumericID IN
(SELECT TRIM(TRAILING 'e' FROM x) AS NumericID 
FROM fruits 
GROUP BY NumericID HAVING COUNT(*) >= 2)

Open in new window


(Edited)
0
 
LVL 35

Accepted Solution

by:
Terry Woods earned 500 total points
ID: 41815102
This worked in the sqlfiddle (the previous version didn't):

SELECT `Name`, TRIM(TRAILING 'e' FROM x) AS `NumericID`
FROM fruits 
WHERE TRIM(TRAILING 'e' FROM x) IN
(SELECT TRIM(TRAILING 'e' FROM x) AS NumericID 
FROM fruits 
GROUP BY TRIM(TRAILING 'e' FROM x) HAVING COUNT(*) >= 2)

Open in new window

0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 41815106
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Creating and Managing Databases with phpMyAdmin in cPanel.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question