95

I want to select rows that have a distinct email, see the example table below:

+----+---------+-------------------+-------------+
| id | title   | email             | commentname |
+----+---------+-------------------+-------------+
|  3 | test    | [email protected]   | rob         |
|  4 | i agree | [email protected]   | rob         |
|  5 | its ok  | [email protected]   | rob         |
|  6 | hey     | [email protected]   | rob         |
|  7 | nice!   | [email protected] | simon       |
|  8 | yeah    | [email protected]  | john        |
+----+---------+-------------------+-------------+

The desired result would be:

+----+-------+-------------------+-------------+
| id | title | email             | commentname |
+----+-------+-------------------+-------------+
|  3 | test  | [email protected]   | rob         |
|  7 | nice! | [email protected] | simon       |
|  8 | yeah  | [email protected]  | john        |
+----+-------+-------------------+-------------+

Where I don't care which id column value is returned. What would be the required SQL?

Improve this question
0

5 Answers 5

Reset to default
121

Quick one in TSQL

SELECT a.*
FROM emails a
INNER JOIN 
  (SELECT email,
    MIN(id) as id
  FROM emails 
  GROUP BY email 
) AS b
  ON a.email = b.email 
  AND a.id = b.id;
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Wow that was fast guys!:) laptop's answer was the shortest and easiest, thanks!
The distinct keyword is not necessary here. Also, it seems like a join on just id would do the trick as well.
I have a huge table with primary key an aggregate of two columns, it is not working in that case
@downvoter , what do you mean by not working, perhaps will be another question?
Excellent, I changed the min to max to get the last row in the duplicate instead of first
45

I'm assuming you mean that you don't care which row is used to obtain the title, id, and commentname values (you have "rob" for all of the rows, but I don't know if that is actually something that would be enforced or not in your data model). If so, then you can use windowing functions to return the first row for a given email address:

select
    id,
    title,
    email,
    commentname

from
(
select 
    *, 
    row_number() over (partition by email order by id) as RowNbr 

from YourTable
) source

where RowNbr = 1
Improve this answer

3 Comments

This is the best solution, because it can apply to duplicate rows that do not have a unique identity column, or ones that do.
....Yes this solved the issue for me....the solution above only grouped the table data together.....i.e for Microsoft SQL 2008 Server/data .........thanks Adam......
This is a really good solution that works great for smaller tables. Is there a way to do this without having to list each column in the SELECT statement?
6

If you are using MySql 5.7 or later, according to these links (MySql Official, SO QA), we can select one record per group by with out the need of any aggregate functions.

So the query can be simplified to this.

select * from comments_table group by commentname;

Try out the query in action here

Improve this answer

4 Comments

Unfortunately, the question is tagged with tsql and sqlserver.
Even though it was the right answer to the wrong question I ended up here looking for this solution for mysql so take my updoot
Nice solution deserves more respects
didn't work with mysql Ver 8.0.29-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))
3

Since you don't care which id to return I stick with MAX id for each email to simplify SQL query, give it a try

;WITH ue(id)
 AS
 (
   SELECT MAX(id)
   FROM table
   GROUP BY email
 )
 SELECT * FROM table t
 INNER JOIN ue ON ue.id = t.id
Improve this answer

Comments

-2

SELECT * FROM emails GROUP BY email;

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.