44

How can I use the DISTINCT clause with WHERE? For example:

SELECT * FROM table WHERE DISTINCT email; -- email is a column name

I want to select all columns from a table with distinct email addresses.

Michael Petrotta
  • 59,888
  • 27
  • 145
  • 179
Mohit
  • 1,204
  • 2
  • 13
  • 19
  • 1
    That's contradiction in terms. Do you mean "SELECTing all columns whose email is unique"? – Adam Matan Apr 10 '11 at 08:19
  • see updated query means I have updated my query in my answer that solves your problem. – Harry Joy Apr 10 '11 at 08:34
  • I believe the question is : how do you filter out all the rows but one for each email. So if the table has 100 rows but only 10 unique emails among them I want to select 10 rows. – ILIA BROUDNO Dec 09 '22 at 17:04

11 Answers11

44

If you mean all columns whose email is unique:

SELECT * FROM table WHERE email in
     (SELECT email FROM table GROUP BY email HAVING COUNT(email)=1);
Adam Matan
  • 128,757
  • 147
  • 397
  • 562
  • 3
    is it wrong if I write `SELECT * FROM table where email in(select distinct email from table)` ? – Ravi Jul 24 '13 at 10:54
  • @jWeaver...Even I was thinking initially what you were thinking. But, tried executing the query. It is wrong. – Vikram Aug 26 '13 at 16:16
  • @Delfino a `GROUP BY` partitions the result set based on the email string, and applies the `HAVING` as a sort of to decide which partitions to return. Only partitions with a single row in them, i.e. unique mails, are returned by the subquery. – Wolfzoon May 29 '19 at 14:10
7

May be by :

SELECT DISTINCT email,id FROM table where id='2';
Harry Joy
  • 58,650
  • 30
  • 162
  • 207
  • @Mohit: what do you wanna say? elaborate please? – Harry Joy Apr 10 '11 at 08:24
  • 1
    i want to select all rows having unique email and having id='2'; – Mohit Apr 10 '11 at 08:25
  • email and id are two columns in my table – Mohit Apr 10 '11 at 08:26
  • i don't want to update, i just want to select the rows from my table !! with unique email and having id='2'; for example : - SELECT * FROM TABLE WHERE DISTINCT (email) AND id='2'; I know the above query is wrong but i just want to do something as in above query – Mohit Apr 10 '11 at 08:29
  • @Mohit: see updated query means I have updated my query in my answer that solves your problem. – Harry Joy Apr 10 '11 at 08:44
4

Try:

SELECT * FROM table GROUP BY email

  • This returns all rows with a unique email taken by the first ID appearance (if that makes sense)
  • I assume this is what you were looking since I had about the same question but none of these answers worked for me.
Alec Scott
  • 51
  • 2
  • This will work only if the only column in table is [email] as only columns from GROUP BY conditions or aggregations may be in SELECT list – HoGo Apr 23 '19 at 19:44
4
select t1.*
from YourTable as t1
  inner join
    (select email
     from YourTable
     group by email
     having count(email) = 1 ) as t2
    on t1.email = t2.email
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
3

You can use the HAVING clause.

SELECT * 
FROM tab_name
GROUP BY email_id
HAVING COUNT(*) = 1;
SilverNak
  • 3,283
  • 4
  • 28
  • 44
that1girl.cyborg
  • 39
  • 2
1

You can use ROW_NUMBER(). You can specify where conditions as well. (e.g. Name LIKE'MyName% in the following query)

SELECT  *
FROM    (SELECT ID, Name, Email,
            ROW_NUMBER() OVER (PARTITION BY Email ORDER BY ID) AS RowNumber
     FROM   MyTable
     WHERE  Name LIKE 'MyName%') AS a
WHERE   a.RowNumber = 1
Kamyar
  • 18,639
  • 9
  • 97
  • 171
0

One simple query will do it: 

SELECT * 
FROM table 
GROUP BY email 
HAVING COUNT(*) = 1;
Lu Ji
  • 1
0

Wouldn't this work:

 SELECT email FROM table1 t1 
          where UNIQUE(SELECT * FROM table1 t2);
caitan correia
  • 41
  • 1
  • 11
  • I'm not aware of many DBMS' that have implemented the UNIQUE predicate. Which one are you using? Also, I think you got the queries backwards: shouldn't `SELECT email` be wrapped in the `UNIQUE()` predicate? And I think you need some kind of join clause (`FROM table1 t2 WHERE t2.email = t1.email`). – JDB Aug 20 '16 at 03:45
0

If you have a unique column in your table (e.g. tableid) then try this.

SELECT EMAIL FROM TABLE WHERE TABLEID IN 
(SELECT MAX(TABLEID), EMAIL FROM TABLE GROUP BY EMAIL)
AhmedRana
  • 486
  • 9
  • 22
-3

Query:

Select *, (Select distinct email) from Table1
toha
  • 5,095
  • 4
  • 40
  • 52
James
  • 1
-3

SELECT DISTINCT dbo.Table.Email,dbo.Table.FirstName dbo.Table.LastName, dbo.Table.DateOfBirth (etc) FROM dbo.Table.Contacts WHERE Email = 'name@email';

Ivan
  • 1