+- 0Day Forums (https://0day.red)
+-- Forum: Coding (https://0day.red/Forum-Coding)
+--- Forum: Database (https://0day.red/Forum-Database)
+---- Forum: MySQL (https://0day.red/Forum-MySQL)
+---- Thread: Finding duplicate values in MySQL (/Thread-Finding-duplicate-values-in-MySQL)
Finding duplicate values in MySQL - cynethiagsmfvtbdhw - 07-27-2023
I have a table with a varchar column, and I would like to find all the records that have duplicate values in this column. What is the best query I can use to find the duplicates?
RE: Finding duplicate values in MySQL - casement711039 - 07-27-2023
Assuming your table is named TableABC and the column which you want is Col and the primary key to T1 is Key.
SELECT a.Key, b.Key, a.Col
FROM TableABC a, TableABC b
WHERE a.Col = b.Col
AND a.Key <> b.Key
The advantage of this approach over the above answer is it gives the Key.
RE: Finding duplicate values in MySQL - johnathmjtbpkufep - 07-27-2023
Do a `SELECT` with a `GROUP BY` clause. Let's say *name* is the column you want to find duplicates in:
SELECT name, COUNT(*) c FROM table GROUP BY name HAVING c > 1;
This will return a result with the *name* value in the first column, and a count of how many times that value appears in the second.
RE: Finding duplicate values in MySQL - sulekuqwb - 07-27-2023
SELECT
t.*,
(SELECT COUNT(*) FROM city AS tt WHERE tt.name=t.name) AS count
FROM `city` AS t
WHERE
(SELECT count(*) FROM city AS tt WHERE tt.name=t.name) > 1 ORDER BY count DESC
RE: Finding duplicate values in MySQL - clumsy128 - 07-27-2023
SELECT *
FROM `dps`
WHERE pid IN (SELECT pid FROM `dps` GROUP BY pid HAVING COUNT(pid)>1)
RE: Finding duplicate values in MySQL - tickbird429250 - 07-27-2023
To find how many records are duplicates in name column in Employee, the query below is helpful;
Select name from employee group by name having count(*)>1;
RE: Finding duplicate values in MySQL - eugenejeusevbmxt - 07-27-2023
For removing duplicate rows with multiple fields , first cancate them to the new unique key which is specified for the only distinct rows, then use "group by" command to removing duplicate rows with the same new unique key:
Create TEMPORARY table tmp select concat(f1,f2) as cfs,t1.* from mytable as t1;
Create index x_tmp_cfs on tmp(cfs);
Create table unduptable select f1,f2,... from tmp group by cfs;
RE: Finding duplicate values in MySQL - aydasbiv - 07-27-2023
The following will find all product_id that are used more than once. You only get a single record for each product_id.
SELECT product_id FROM oc_product_reward GROUP BY product_id HAVING count( product_id ) >1
Code taken from :
[To see links please register here]
RE: Finding duplicate values in MySQL - kaliedbsxibpqt - 07-27-2023
SELECT DISTINCT a.email FROM `users` a LEFT JOIN `users` b ON a.email = b.email WHERE a.id != b.id;
RE: Finding duplicate values in MySQL - printwssnw - 07-27-2023
CREATE TABLE tbl_master
(`id` int, `email` varchar(15));
INSERT INTO tbl_master
(`id`, `email`) VALUES
(1, '[email protected]'),
(2, '[email protected]'),
(3, '[email protected]'),
(4, '[email protected]'),
(5, '[email protected]');
QUERY : SELECT id, email FROM tbl_master
WHERE email IN (SELECT email FROM tbl_master GROUP BY email HAVING COUNT(id) > 1)