• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 92
  • Last Modified:

MySQL: Get All Duplicates (not grouped)

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
skij
Asked:
skij
  • 3
1 Solution
 
Terry WoodsIT GuruCommented:
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
 
Terry WoodsIT GuruCommented:
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
 
Terry WoodsIT GuruCommented:
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now