DISTINCT clause with WHERE

Question

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.

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

score 39 · Answer 1 · answered Apr 10 '11 at 08:20

39

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);

answered Apr 10 '11 at 08:20

Adam Matan

117,979
135
375
532

2

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

Harry Joy · Answer 2 · 2011-04-10T08:25:48.107

5

May be by :

SELECT DISTINCT email,id FROM table where id='2';

edited Apr 10 '11 at 08:25

answered Apr 10 '11 at 08:18

Harry Joy

57,133
30
158
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

score 3 · Answer 3 · edited Aug 02 '17 at 09:53

3

You can use the HAVING clause.

SELECT * 
FROM tab_name
GROUP BY email_id
HAVING COUNT(*) = 1;

edited Aug 02 '17 at 09:53

SilverNak

3,123
4
26
36

answered Aug 02 '17 at 08:46

that1girl.cyborg

39
2

Alec Scott · Answer 4 · 2019-03-29T09:38:39.410

3

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.

edited Mar 29 '19 at 09:38

answered Feb 26 '19 at 07:54

Alec Scott

41
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

score 3 · Answer 5 · answered Apr 10 '11 at 08:21

3

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

answered Apr 10 '11 at 08:21

Mikael Eriksson

132,594
21
199
273

score 1 · Answer 6 · answered Apr 10 '11 at 08:22

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

score 0 · Answer 7 · answered Jul 31 '15 at 08:53

0

One simple query will do it:

SELECT * 
FROM table 
GROUP BY email 
HAVING COUNT(*) = 1;

answered Jul 31 '15 at 08:53

Lu Ji

1

The op did not specify the flavor of SQL, but this command will not run in SQL Server and produces ambiguous results in MySQL. – Alien Technology Mar 27 '17 at 16:06

score 0 · Answer 8 · answered Aug 20 '16 at 03:18

0

Wouldn't this work:

 SELECT email FROM table1 t1 
          where UNIQUE(SELECT * FROM table1 t2);

answered Aug 20 '16 at 03:18

caitan correia

31
10

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

score 0 · Answer 9 · answered Dec 02 '18 at 12:13

0

simple query this query select all record from table where email is unique:

select distinct email,* from table_name

answered Dec 02 '18 at 12:13

Zahid Tanveer

50
1
11

score 0 · Answer 10 · answered Jan 02 '19 at 14:49

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)

answered Jan 02 '19 at 14:49

AhmedRana

438
8
20

score -2 · Answer 11 · edited Jul 27 '17 at 07:10

-2

Query:

Select *, (Select distinct email) from Table1

edited Jul 27 '17 at 07:10

toha

4,643
3
35
52

answered Jul 27 '17 at 03:00

James

1

score -3 · Answer 12 · answered Jun 14 '19 at 08:05

-3

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

answered Jun 14 '19 at 08:05

Ivan

1

DISTINCT clause with WHERE

12 Answers12

Linked