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
Select all 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
Open in new window
(Edited)