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.
You can filter the top record per group with a correlated subquery:
For performance with this query, you want an index on
(name, id)
.