How do we find the duplicate values available in a MySQL table?

MySQLMySQLi Database

Suppose we have the following table named stock_item in which the column quantity is having duplicate values i.e. for item name ‘Notebooks’ and ‘Pencil’, the column ‘Quantity’ is having duplicate values ‘40’ as shown in the table.

mysql> Select * from stock_item;
+------------+---------+
| item_name  |quantity |
+------------+---------+
| Calculator | 89      |
| Notebooks  | 40      |
| Pencil     | 40      |
| Pens       | 32      |
| Shirts     | 29      |
| Shoes      | 29      |
| Trousers   | 29      |
+------------+---------+
7 rows in set (0.00 sec)

Now with the help of the following query, we can find the duplicate values in column ‘quantity’ along with the name of items.

mysql> Select distinct g.item_name,g.quantity from stock_item g
    -> INNER JOIN Stock_item b ON g.quantity = b.quantity
    -> WHERE g.item_name<>b.item_name;

+-----------+----------+
| item_name | quantity |
+-----------+----------+
| Pencil    | 40       |
| Notebooks | 40       |
| Shoes     | 29       |
| Trousers  | 29       |
| Shirts    | 29       |
+-----------+----------+
5 rows in set (0.00 sec)
raja
Published on 21-Feb-2018 12:38:21
Advertisements