Hi all,
I have to follow up on my own mail:
Joerg Bruehe wrote:
[[...]]
For tabular form, try along these lines (untested):
SELECT MIN(id), email, id
FROM addressbook
WHERE id > MIN(id)
GROUP BY email
HAVING COUNT(*) > 1
I cannot explain what made me write that, other than lack of concentration:
This *cannot* achieve what Yashesh asked for, because it creates only
one line per group ("email") and so can report only one "id" value.
MySQL allows such a statement
(an extension over the standard, which does not allow "id" in the column
list because it is neither the grouping column nor a group function),
but for an "email" that occurs with three (or more) "id" values it will
report only one of these duplicates.
If such an iterative approach is ok (report one duplicate, handle it by
deleting one; repeat that), the standard SQL way would be:
SELECT MIN(id), email, MAX(id)
FROM addressbook
GROUP BY email
HAVING COUNT(*) > 1
But if one report should show all duplicates, either the join proposed
in another reply or the group_concat() in my first mail should be used.
Jörg
--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
