Home / Questions / 557019
Accepted
ArcanisGK507
Asked: 2022-09-15 12:30:26 +0800 CST 2022-09-15 12:30:26 +0800 CST

Get (View) Duplicate Records in MySQL (Single Column)

  • 772

I have been researching how to find duplicates of a record in a database and in that I find this answer:

Finding Duplicates in MySQL - This answer aims to find duplicates in multiple columns of the same table, while I look for duplicates in a single column.

But I don't achieve my goal. what I intend is to visualize the N duplicate records.

Structure and example data:

DROP TABLE IF EXISTS `prueba`;
CREATE TABLE IF NOT EXISTS `prueba` (
  `id` bigint(21) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Id de la tabla',
  `name` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'nombre',
  `prod_ft` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'codigo',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT;
SET FOREIGN_KEY_CHECKS=1;

INSERT INTO `prueba` VALUES 
(1, 'Pepe Pelotas 1', 'FT202201001'),
(2, 'Pepe Pelotas 2', 'FT202201001'),
(3, 'Pepe Pelotas 3', 'FT202201003'),
(4, 'Pepe Pelotas 4', 'FT202201004'),
(5, 'Pepe Pelotas 5', 'Actualizar');

Query:

SELECT * FROM prueba WHERE `prod_ft` != 'Actualizar' GROUP BY `prod_ft` HAVING COUNT(*) > 1;

Result:

enter image description here

Example in sqlfiddle

Expected result:

enter image description here

mysql

1 Answers

  1. Best Answer

    First you should see that pord_ftit is repeated, the most consistent with the SQL standard and that Mysql refuses to respect is like this:

    SELECT prod_ft 
       FROM prueba 
       WHERE `prod_ft` != 'Actualizar' 
       GROUP BY `prod_ft` 
       HAVING COUNT(*) > 1;

    If you already have them prod_ftjust filter the rows that match these:

    SELECT *
       FROM prueba 
       WHERE prod_ft IN (SELECT prod_ft 
                                FROM prueba 
                                WHERE `prod_ft` != 'Actualizar' 
                                GROUP BY `prod_ft` 
                                HAVING COUNT(*) > 1
       )

    With inner join:

    SELECT *
       FROM prueba p1
       INNER JOIN (SELECT prod_ft 
                                FROM prueba 
                                WHERE `prod_ft` != 'Actualizar' 
                                GROUP BY `prod_ft` 
                                HAVING COUNT(*) > 1
                   ) p2
                   ON p1.prod_ft = p2.prod_ft
    • 2