On 12-11-10 20:15, jr wrote:
Your query is basically the same as:

SELECT * FROM ci2
WHERE ndc is not null
AND (SELECT count(*) from ci2 GROUP BY businessunit,zonenm,zoneid,ndc)>1

And this can be rewritten as:
DELETE FROM ci2
WHERE ndc is not null
AND (SELECT count(*) from ci2 GROUP BY businessunit,zonenm,zoneid,ndc)>1

(untested, please backup your data first ;-)

--
Luuk

On Nov 13, 8:19*am, Luuk <L... (AT) invalid (DOT) lan> wrote:
tnx,

On Nov 13, 8:19*am, Luuk <L... (AT) invalid (DOT) lan> wrote:
Hello, I still need the answer on this. Is this right?

SELECT businessunit,zonenm,zoneid,sku,ndc, count(*)
from ci2
group by businessunit,ndc
having COUNT(concat(bu,ndc) > 1)

It is not doing what I expect.
The count column has mostly 1s, but shows some 2s (dupes)
but I need all of those > 1 in a aggregate list.

thanks,

On Wed, 17 Nov 2010 12:42:09 -0800 (PST), jr wrote:
I do not think this will do what the poster wants it to. That will not
delete duplicates in the sense that there will be only one row of the
duplicates left. That will delete ALL the rows that have the duplicate
values, leaving no row that has those previously-duplicated values.

That is one of the best indications that it is not right.

The case where *all* the values are duplicated is trivial. That's what
SELECT DISTINCT is for. Do that into a copy of the table, delete the
old one, rename the new, and you're done.

The problem ones are where you have some values that are duplicate and
some that are not. They're duplicate by the values that matter to you,
or you want to save some particular quality above others. Maybe you want
to save the oldest one by a timestamp, or the highest value by customer
number. In this example, we will save by the highest value in rowid.

I'm going to do a real example. Sadly, this means I have to use my data,
not yours, since I want to actually do the operations and show what's
happening.

============================================
$ mysql -u hellsop -p test
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 149646
Server version: 5.0.33-log OpenBSD port: mysql-server-5.0.33

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from trashheap2;
+-------+--------------+----------+---------------------------------+
8 rows in set (0.00 sec)
============================================

Nice table, yes? Manageable. Has a couple of traps for the unwary. If
you are seeing it in my terminal, it's a pleasing aqua color too. Pay
no attention to the contents; they are unimportant. The rowid is not
going to be considered a duplicate value, since there are none. But we
will want to keep the highest of those, to prove that we're actually in
control of the process.

Here is how to get the rows that we want to keep:

============================================
mysql> SELECT a.* FROM trashheap2 a
-> JOIN (SELECT *, MAX(rowid) AS max_id
-> FROM trashheap2
-> GROUP BY parent_rowid, label, `data`) b
-> ON (a.parent_rowid = b.parent_rowid OR
-> (a.parent_rowid IS NULL AND b.parent_rowid IS NULL)) AND
-> a.label = b.label AND
-> a.`data` = b.`data` AND
-> a.rowid = b.max_id
-> ORDER BY rowid;
+-------+--------------+----------+---------------------------------+
6 rows in set (0.02 sec)
============================================

Let's look at the query.

SELECT a.* FROM trashheap2 a
-- Here we join the table to a phantom copy of itself.
-- max_id is how we want to pick which row to keep.
JOIN (SELECT *, MAX(rowid) AS max_id
FROM trashheap2
GROUP BY parent_rowid, label, `data`) b
-- First, we need to connect it all up by our duplicate columns
ON (a.parent_rowid = b.parent_rowid OR
-- (We have to pull a few shenanigans here because joining on null
-- values doesn't actually match in a join. Comparing NULL to NULL
-- returns NULL, not TRUE...)
(a.parent_rowid IS NULL AND b.parent_rowid IS NULL)) AND
a.label = b.label AND
a.`data` = b.`data` AND
-- And here we set our criteria for which row of the duplicate ones
-- we want to keep.
a.rowid = b.max_id
ORDER BY rowid;

Now, if you feel lazy and have the storage to do it, you can select that
into a fresh table like the SELECT DISTINCT one above. The drawback is
that it kind of depends on being able to interrupt things long enough do
all this table swapping. If you can't, you pull the same trick again:
You build a delete query that joins to the above query, and delete the
rows where the second subquery result is NULL. That means you have
opened the same table a total of three times in the same query, have
nested subqueries two deep and have four table aliases...

DELETE mstr
FROM trashheap2 mstr
LEFT OUTER JOIN (
SELECT a.*, b.max_id FROM trashheap2 a
JOIN (SELECT *, COUNT(*) AS cnt, MAX(rowid) AS max_id
FROM trashheap2
GROUP BY parent_rowid, label, `data`) b
ON (a.parent_rowid = b.parent_rowid OR
(a.parent_rowid IS NULL AND b.parent_rowid IS NULL)) AND
a.label = b.label AND
a.`data` = b.`data` AND
a.rowid = b.max_id
ORDER BY rowid
) sub
ON mstr.rowid = sub.rowid AND
-- same NULL shenanigans here again....
(mstr.parent_rowid = sub.parent_rowid OR
(mstr.parent_rowid IS NULL AND sub.parent_rowid IS NULL)) AND
mstr.label = sub.label AND
mstr.`data` = sub.`data`
WHERE sub.rowid IS NULL ;

And, sure enough, this works how we want it to:

============================================
mysql> DELETE mstr
-> FROM trashheap2 mstr
-> LEFT OUTER JOIN (
-> SELECT a.*, b.max_id FROM trashheap2 a
-> JOIN (SELECT *, COUNT(*) AS cnt, MAX(rowid) AS max_id
-> FROM trashheap2
-> GROUP BY parent_rowid, label, `data`) b
-> ON (a.parent_rowid = b.parent_rowid OR
-> (a.parent_rowid IS NULL AND b.parent_rowid IS NULL)) AND
-> a.label = b.label AND
-> a.`data` = b.`data` AND
-> a.rowid = b.max_id
-> ORDER BY rowid
-> ) sub
-> ON mstr.rowid = sub.rowid AND
-> (mstr.parent_rowid = sub.parent_rowid OR
-> (mstr.parent_rowid IS NULL AND sub.parent_rowid IS NULL)) AND
-> mstr.label = sub.label AND
-> mstr.`data` = sub.`data`
-> WHERE sub.rowid IS NULL ;
Query OK, 2 rows affected (0.02 sec)

mysql> SELECT * FROM trashheap2;
+-------+--------------+----------+---------------------------------+
6 rows in set (0.01 sec)
============================================

Good luck!

--
Liberty, equality, diversity. Pick any two.

On 18-11-10 02:55, Peter H. Coffin wrote:
you are correct, my query is wrong....

--
Luuk