StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POFind duplicate records in table by more than one criteria
text
Body
copied!<pre><code>+-------------------------+ | SKU | UPC | NAME | INVID +-------------------------+ | 001 | 111 | NAME1 | 1 | 001 | null | NAME2 | 2 | 0-1 | 111 | NAME3 | 3 | -01 | 111 | NAME4 | 4 | 002 | 222 | NAME5 | 5 | 002 | null | NAME6 | 6 | 003 | 333 | NAME7 | 7 +-------------------------+ </code></pre> <p>I want to find duplicate records in this table by two criterias. First is SKU, second is UPC. At this case duplicate records are [1,2,3,4] and [5,6]. Rows [1,2] are duplicate because sku numbers are matched. Rows [1,3,4] are duplicates because UPC numbers are matched. The first duplicate group should look like this:</p> <p><strong>GROUP1*</strong></p> <pre><code>+-------------------------+ | 001 | 111 | NAME1 | 1 | 001 | null | NAME2 | 2 | 0-1 | 111 | NAME3 | 3 | -01 | 111 | NAME4 | 4 +-------------------------+ </code></pre> <p>The second duplicate group should look like this:</p> <p><strong>GROUP2*</strong></p> <pre><code>+-------------------------+ | 002 | 222 | NAME5 | 5 | 002 | null | NAME6 | 6 +-------------------------+ </code></pre> <p>How can I found this groups with mysql query ? If I found duplicates records I set a grouID*<em>*</em>* of any duplicate record in table.</p> <p>I tried this, but not show all duplicates. </p> <pre><code>select sku, upc from inv WHERE sku is not null GROUP BY sku, upc HAVING count(sku) > 1 OR count(upc) > 1 </code></pre> <p>This is SQL schema:</p> <p><a href="http://sqlfiddle.com/#!2/4d760/2" rel="nofollow">http://sqlfiddle.com/#!2/4d760/2</a></p> <p>This query return empty result.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload