select unique rows based on single distinct column

问题:

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

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

The desired result would be:

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

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

回答1:

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;

回答2:

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

回答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

回答4:

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