[sql] DISTINCT clause with WHERE

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.

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)

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

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

Query:

Select *, (Select distinct email) from Table1

May be by :

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

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

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   

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

select distinct email,* from table_name

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.

Wouldn't this work:

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

You can use the HAVING clause.

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

One simple query will do it:

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