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      rob hotmail com     rob              4   i agree   rob hotmail com     rob              5   its ok    rob hotmail com     rob              6   hey       rob hotmail com     rob              7   nice      simon hotmail com   simon            8   yeah      john hotmail com    john           ---- --------- ------------------- -------------    The desired result would be    ---- ------- ------------------- -------------    id   title   email               commentname    ---- ------- ------------------- -------------     3   test    rob hotmail com     rob              7   nice    simon hotmail com   simon            8   yeah    john hotmail com    john           ---- ------- ------------------- -------------    Where I don t care which id column value is returned  What would be the required SQL

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] select unique rows based on single distinct column

Examples related to sql-server

Examples related to tsql