select unique rows based on single distinct column

Question

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?

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

Answer 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