Unique but the latest rows

Question

I am using mysql 5.7 server and I have the following table with id as PRIMARY KEY.

My Table

mysql> select * from abc;
+----+---------+-------+
| id | name    | place |
+----+---------+-------+
|  1 | asd1    | abcd1 |
|  2 | asd2    | abcd1 |
|  3 | asd1    | abcd2 |
|  4 | asd3    | abcd1 |
|  5 | asd4    | abcd1 |
|  6 | asd1    | abcd1 |
|  7 | asd2    | abcd2 |
|  8 | asd3    | abcd2 |
|  9 | asd4    | abcd2 |
| 10 | asd3    | abcd1 |
| 11 | asd4    | abcd1 |
| 12 | asd4    | abcd2 |
| 13 | asd1    | abcd2 |
+----+---------+-------+
13 rows in set (0.00 sec)

Expected Result

I want to select the rows with unique name having the latest id.

I other words, my expected result should be as following:

+----+---------+-------+
| id | name    | place |
+----+---------+-------+
| 13 | asd1    | abcd2 |
|  7 | asd2    | abcd2 |
| 10 | asd3    | abcd1 |
| 12 | asd4    | abcd2 |
+----+---------+-------+
4 rows in set (0.00 sec)

What I tried

mysql> select * from abc group by name order by id desc;
+----+------+-------+
| id | name | place |
+----+------+-------+
|  5 | asd4 | abcd1 |
|  4 | asd3 | abcd1 |
|  2 | asd2 | abcd1 |
|  1 | asd1 | abcd1 |
+----+------+-------+
4 rows in set (0.00 sec)

mysql> select * from (select * from abc as t order by t.id desc) as st1 group by t1.orderID;
+----+------+-------+
| id | name | place |
+----+------+-------+
|  1 | asd1 | abcd1 |
|  2 | asd2 | abcd1 |
|  4 | asd3 | abcd1 |
|  5 | asd4 | abcd1 |
+----+------+-------+
4 rows in set (0.00 sec)

mysql> select * from abc l inner join (select * from abc group by name) r on l.id = r.id;
+----+------+-------+----+------+-------+
| id | name | place | id | name | place |
+----+------+-------+----+------+-------+
|  1 | asd1 | abcd1 |  1 | asd1 | abcd1 |
|  2 | asd2 | abcd1 |  2 | asd2 | abcd1 |
|  4 | asd3 | abcd1 |  4 | asd3 | abcd1 |
|  5 | asd4 | abcd1 |  5 | asd4 | abcd1 |
+----+------+-------+----+------+-------+
4 rows in set (0.01 sec)

Question

What will be the correct SQL to get the Expected result? Precise and simple one will be preferred.

Answer 1

You can filter the top record per group with a correlated subquery:

select a.*
from abc a
where a.id = (select max(a1.id) from abc a1 where a1.name = a.name)
order by a.name

For performance with this query, you want an index on (name, id).

Answer 2

Do you need below -

SELECT MAX(id), name
FROM abc
GROUP BY name

Answer 3

SELECT t1.*
FROM abc t1
NATURAL JOIN ( SELECT MAX(id) id, name
               FROM abс
               GROUP BY name ) t2

fiddle with NATURAL JOIN

fiddle with JOIN .. USING

fiddle with JOIN .. ON

fiddle with cartesian and WHERE