4

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

10

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>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.