CODEWITHSUNDEEP
sqlwhere-clause
Joshua
Joshua

Reputation: 287

Where Statement w/ Distinct

I have a large table but for the purposes of this question, let's assume I have the follwoing column strucure:

enter image description here

I'd like to have a Where statement that returns only rows where the e-mail address is distinct in that particular column.

Thoughts?

Upvotes: 0

Views: 97

Answers (3)

John Woo
John Woo

Reputation: 263783

SELECT BillingEMail
FROM   tableName
GROUP  BY BillingEMail
HAVING COUNT(BillingEMail) = 1

OR HAVING COUNT(*) = 1

I don't know what RDBMS you are using (the reason why i can't introduce of using analytical functions) but you can do this by joining with a subquery if you want to get all columns

SELECT a.*
FROM   tableName a
       INNER JOIN
      (
        SELECT BillingEMail
        FROM   tableName
        GROUP  BY BillingEMail
        HAVING COUNT(BillingEMail) = 1 
      )b ON a.BillingEMail = b.BillingEMail

Upvotes: 3

RB.
RB.

Reputation: 37202

I prefer JW's approach, but here is another one using NOT EXISTS.

SELECT AccountID, [Billing Email]
FROM   table t1
WHERE  NOT EXISTS (
    -- Make sure that no other row contains the same
    -- email, but a different Account ID.
    SELECT 1
    FROM   table t2
    WHERE  t1.[Billing Email] = t2.[Billing Email]
    AND    t1.AccountID <> t2.AccountID
)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270301

In most databases, you can do this

select t.AccountId, t.BillingEmail
from (select t.*, count(*) over (partition by BillingEmail) as cnt
      from t
     ) t
where cnt = 1

The advantage of this approach is that you can get as many columns as you like from the table.

Upvotes: 2

Related Questions