Solved

MySQL: Get All Duplicates (not grouped)

Posted on 2016-09-25
3
48 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
  • 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

929 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now