GROUP BY question

I have a fairly simple question about GROUP BY. I'm sure other people have had the same problem, but I couldn't find any related questions in a DejaNews search. Pardon if this is a FAQ, but...

Assume a table that has three fields: name, ID, and e-mail address. ID is a sequence-- no two rows have the same ID#.

How would I go about printing all the info(name, id, and e-mail) about all the people who share an e-mail address (the address is listed more than once)?

select email, count(*)
from table
group by email
having count(*) > 1

That query gives me a listing of all the duplicate e-mail addresses. This is close, but I need the names and IDs too. The following query doesn't work, because the names and id's for a particular e-mail address are not the same. Hence, count(*) always equals 1.

select name, id, email, count(*)
from table
group by name, id, email
having count(*) > 1

This query works, providing the inner select list isn't very large (it is, around 25,000 records).

select name, id, email
from table
where email in
 (select email, count(*)
  from table
  group by email
  having count(*) > 1
 )

What's an efficient, workable way of doing this? Using MINUS?

Any suggestions would be appreciated.

Thanks.

Jim. Received on Tue Oct 14 1997 - 00:00:00 CDT