Mysql – Getting unique names when the IDs are different (DISTINCT ?)

MySQLselect

I've got this table:

id  | name
1   | John
2   | John
3   | Peter
4   | Mike
5   | Mike

And I want to get the unique names, getting the latest one in the list. I could go for:

SELECT DISTINCT name FROM table ORDER BY id DESC LIMIT 2

This would get me: Mike and Peter. All good. However, I also need the IDs, so then DISTINCT does not work anymore. What would be the right query to get:

5   |   Mike
3   |   Peter

In words: the latest 2 entries that are unique

Best Answer

Here is the query

SELECT id,name FROM
(SELECT name,MAX(id) id FROM `mytable` GROUP BY name) A
ORDER BY id DESC LIMIT 2;

Here is some sample data 

mysql> use test
Database changed
mysql> drop table if exists mytable;
Query OK, 0 rows affected (0.03 sec)

mysql> create table mytable
    -> (id int not null auto_increment,
    -> name varchar(20),primary key (id));
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO mytable (name) VALUES
    -> ('John'),('John'),('Peter'),('Mike'),('Mike');
Query OK, 5 rows affected (0.07 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM mytable;
+----+-------+
| id | name  |
+----+-------+
|  1 | John  |
|  2 | John  |
|  3 | Peter |
|  4 | Mike  |
|  5 | Mike  |
+----+-------+
5 rows in set (0.00 sec)

mysql> SELECT id,name FROM
    -> (SELECT name,MAX(id) id FROM `mytable` GROUP BY name) A
    -> ORDER BY id DESC LIMIT 2;
+------+-------+
| id   | name  |
+------+-------+
|    5 | Mike  |
|    3 | Peter |
+------+-------+
2 rows in set (0.00 sec)

mysql>
Related Question