Hello.
On Mon 2002-12-09 at 13:45:42 +0200, [EMAIL PROTECTED] wrote:
> Please can someone help me with this:
>
> I need to "filter" duplicate entries from my result query, but there's a
> twist...
>
[...]
> Now I want to search the table for all unique name/key combinations.
>
> I tried
> select DISTINCTROW name,key from table order by name;
>
> but, it returns
> name1 key1
> name2 key2
> name3 key3
> name 1 key1
>
> Where name 1 key1 was obviously a typo, yet, I would ideally want to
> filter or flag this through some kind of logic => ( there is already a
> key1 associated with a name1, cannot associate key1 with another name!)
Well, I suggest you do that before you create the report by cleaning
up the database. It makes more sense to repair it once, instead every
time you query the database:
SELECT key, COUNT(name) AS cnt FROM your_table GROUP BY key HAVING cnt > 1
which will list all keys which have more than one name associated. Then use
SELECT key, name FROM your_table
WHERE key IN (key3, key5) GROUP BY key ORDER BY key
where you replace key3, key5 with the results from the first query.
This will list the alternatives. Decide which one is the "right" one
and update the false one accordingly:
UPDATE your_table SET name = name1 WHERE key = key1;
where you insert the appropriate values for name1 and key1, of course.
> Firstly, my problem is with the query as I quoted it itself, ie, is that
> the best way to prevent duplicates?
The best way to prevent duplicates is to already prevent them on
insertion, which you would do with a UNIQUE key on (key,name).
For selects the one you cited above is fine.
> I might also try this:
>
> select DISTINCTROW MAX(id),name,key from table group by name;
> but this will only ensure that only the LATEST key/name "pair" is listed
> right?
No. That won't work. You will get MAX(id) and a random name and key
from the group. That is, in Standard SQL, you may not mix grouping
functions with columns that are not in the GROUP BY clause, while
MySQL allows it and just gives you random values (it presumes you are
sure that name,key will be same for all values in a group).
The correct query would use a sub-select which you work-around with
temporary tables in MySQL. See the tutorial section in the manual for
examples.
HTH,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
