Select Unique Rows Based on Single Distinct Column - MySQL

Question

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

Table Name = Users

 +----+---------+-------------------+-------------+
   | 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 |
   +----+-------+-------------------+-------------+
   |  5 | its ok| [email protected]   | rob         |
   |  7 | nice! | [email protected] | simon       |
   |  8 | yeah  | [email protected]  | john        |
   +----+-------+-------------------+-------------+

Distinct value should be latest entry in Table Example id = 6 What would be the required SQL?

Wouldn't latest row be id = 6?

Mr ML
– Mr ML

2019-01-18 10:39:21 +00:00
Commented Jan 18, 2019 at 10:39 — Mr ML
– Mr ML, Commented Jan 18, 2019 at 10:39
my mistake it should be 6

Aadil Shaikh
– Aadil Shaikh

2019-01-18 10:42:13 +00:00
Commented Jan 18, 2019 at 10:42 — Aadil Shaikh
– Aadil Shaikh, Commented Jan 18, 2019 at 10:42

Tim Biegeleisen · Accepted Answer · 2019-01-18 10:40:02Z

4

If you are using MySQL 5.7 or earlier, then you may join your table to a subquery which finds the most recent record for each email:

SELECT t1.id, t1.title, t1.email, t1.commentname
FROM yourTable t1
INNER JOIN
(
    SELECT email, MAX(id) AS latest_id
    FROM yourTable
    GROUP BY email
) t2
    ON t1.email = t2.email AND t1.id = t2.latest_id;

If you are using MySQL 8+, then just use ROW_NUMBER here:

WITH cte AS (
    SELECT id, title, email, commentname,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY id DESC) rn
    FROM yourTable
)

SELECT id, title, email, commentname
FROM cte
WHERE rn = 1;

Note: Your expected output probably has a problem, and the id = 6 record is the latest for [email protected].

answered Jan 18, 2019 at 10:40

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cid Over a year ago

Nice ones :) I suggest you to sort by IDs on second query (tested and the results are sorted such as ids are 8, 6, 7)

Fahmi · Accepted Answer · 2019-01-18 10:44:47Z

2

You can try below using correlated subquery

select * from table1 a
where id in (select max(id) from table1 b where a.email=b.email group by b.email)

edited Jan 18, 2019 at 10:44

answered Jan 18, 2019 at 10:41

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Comments

劉鎮瑲 · Accepted Answer · 2019-08-03 15:54:01Z

0

If 'id' is unique or primary key you could use this one:

select * from Users where id in (select max(id) from Users group by commentname)

Above one would up your database performance because the correlated subqueries comes from the fact that the subquery uses information from the outer query and the subquery executes once for every row in the outer query.So,I will suggest you using my answer if 'id' is unique.

answered Aug 3, 2019 at 15:54

劉鎮瑲

59712 silver badges21 bronze badges

Collectives™ on Stack Overflow

Select Unique Rows Based on Single Distinct Column - MySQL

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related