craschworks

[Note that unless you have an interest in SQL, this post will probably bore you to tears. It probably will even if you do. Beware.]

Suppose you have a table that contains multiple loginnames and email addresses. All loginnames are unique, but two or more loginnames may be associated with the same email address. For example, suppose you have the table “members” populated with the following rows:

loginname | email
—————————–
crash [email protected]
trasch [email protected]
mrasch [email protected]
srasch [email protected]

How would you select all of the rows that contain an email address that appears two or more times in the table? Such that, given the table above, you get the following rows:

loginname | email
crasch [email protected]
mrasch [email protected]

Note that

* I can't use temporary tables or views.

* Each row is unique (they all have different primarykeys).

* I'm using Frontbase on Mac OS X 10.3 which implements most of SQL-92. However, it does not seem to have ROWID's, which precludes a solution like this:

select a.*
   from foo a
   where a.ROWID not in (select min(b.ROWID)
      from foo b
      where a.email = b.email)

Which appears to be a common solution to this problem in Oracle.

This is the solution I've come up with so far:

select loginname, email
   from members
   where email not in (select email
      from members
      group by email
      having count(*) = 1);

I first select all the rows that have a unique email address. Then I select the rows that are not in that set (and therefore must be an email address that is duplicated one or more time). This seems to work, but is quite slow.

Can anyone suggest a more efficient solution?