mysql find duplicates by particular column

other
voted: 0
vote
mysql select duplicates find

mysql find duplicates by particular column

//crete sample database
CREATE TABLE IF NOT EXISTS `product` (
`id` int(10) unsigned NOT NULL,
  `product_code` varchar(255) NOT NULL,
  `product_name` varchar(255) NOT NULL,
  `product_stock` int(11) NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

INSERT INTO `product` (`id`, `product_code`, `product_name`, `product_stock`) VALUES
(1, '1234', 'lcd tv 18"', 5),
(2, 'abcd', 'lcd tv 20"', 8),
(3, '1234', 'lcd monitor 18"', 5),
(4, '4521', 'lcd monitor 20', 41),
(5, '4521', 'lcd monitor 21', 12)
;

//products with id : 1, 3 and 4, 5 will have product_id duplicated

//to find duplicated products by product_id use this query:

SELECT * FROM product HAVING COUNT(product_code) > 1

//to find not distinct list of products by product_code (list of all duplicates) use this query:

select * from product where product_code in (select product_code from product group by product_code having COUNT(*)>1)
working url: not entered
version: not entered
Leave a Comment:
Characters left:
No comments yet.