troubleshooting Question

MySQL: Get All Duplicates (not grouped)

Avatar of skij
skijFlag for Canada asked on
MySQL Server
3 Comments1 Solution143 ViewsLast Modified:
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
For example, the query above returns this:      
Peach
Raspberry
Orange

I want it to return:

Peach
Pear
Orange
Lemon
Lime
Strawberry
Raspberry
Orange
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros