I am using mysql 5.7 server and I have the following table with id
as PRIMARY KEY.
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)
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)
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)
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:
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)
.