---
title: "[TECH] SQL query for LJ brain trust"
date: 2004-06-18
source: livejournal
friends_only: true
original_url: "https://crasch.livejournal.com/259591.html"
---

# [TECH] SQL query for LJ brain trust

*June 18, 2004 · LiveJournal*

\[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 crasch@openknowledge.org trasch trasch@openknoweldge.org mrasch crasch@openknowledge.org srasch srasch@openknowledge.org 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 crasch@openknowledge.org mrasch crasch@openknoweldge.org 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?