PHP - Deleting duplicate values with same ref

Question

I've the following MySQL Table called store

id ref item_no supplier
1  10    x1      usa
2  10    x1      usa
3  11    x1      china
4  12    x2      uk
5  12    x3      uk
6  13    x3      uk
7  13    x3      uk

Now What i'm excepting the output to be is as follows :

id ref item_no supplier
1  10    x1      usa
3  11    x1      china
4  12    x2      uk
5  12    x3      uk
6  13    x3      uk

As you can see item_no x1 and x3 have same ref and supplier source, so what I want is to delete the duplicate record in-order to keep one item_no only !

I've create this PHP code to SELECT results only :

$query1 = "SELECT 
                DISTINCT(item_no) AS field, 
                COUNT(item_no) AS fieldCount, 
                COUNT(ref) AS refcount 
            FROM 
                store 
            GROUP BY item_no HAVING fieldCount > 1";

$result1 = mysql_query($query1);

if(mysql_num_rows($result1)>0){
    while ($row1=mysql_fetch_assoc($result1)) {
        echo $row1['field']."<br /><br />";
    }
} else {
    //IGNORE
}

How to tell the query to SELECT Duplicate records properly according to my needs before creating the DELETE query.

Thanks Guys

Answer 1

You can use the following query to produce the required result set:

SELECT t1.*
FROM store AS t1
JOIN (
   SELECT MIN(id) AS id, ref, item_no
   FROM store
   GROUP BY ref, item_no
) AS t2 ON t1.id > t2.id AND t1.ref = t2.ref AND t1.item_no = t2.item_no 

Demo here

To DELETE you can use:

DELETE t1
FROM store AS t1
JOIN (
   SELECT MIN(id) AS id, ref, item_no
   FROM store
   GROUP BY ref, item_no
) AS t2 ON t1.id > t2.id AND t1.ref = t2.ref AND t1.item_no = t2.item_no 

Answer 2

To find only duplicate records you can use

 SELECT * FROM store WHERE id NOT IN 
 (SELECT id FROM store AS outerStore WHERE id = 
 (SELECT MAX(id) FROM store AS innerStore 
 WHERE outerStore.ref = innerStore.ref AND 
 outerStore.supplier = innerStore.supplier AND outerStore.item_no = innerStore.item_no))

Maybe long, but it should work.