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.

12 Answers

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

May be by :

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

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 

You can use the HAVING clause.

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

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.

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 

One simple query will do it:

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

Wouldn't this work:

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

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

select distinct email,* from table_name

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) 

Query:

Select *, (Select distinct email) from Table1 

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