patternsqlMinor
Getting unique names when the IDs are different (DISTINCT ?)
Viewed 0 times
uniquedistincttheareidsgettingnamesdifferentwhen
Problem
I've got this table:
And I want to get the unique names, getting the latest one in the list. I could go for:
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:
In words: the latest 2 entries that are unique
id | name
1 | John
2 | John
3 | Peter
4 | Mike
5 | MikeAnd 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 2This 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 | PeterIn words: the latest 2 entries that are unique
Solution
Here is the query
Here is some sample data
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>Code Snippets
SELECT id,name FROM
(SELECT name,MAX(id) id FROM `mytable` GROUP BY name) A
ORDER BY id DESC LIMIT 2;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>Context
StackExchange Database Administrators Q#19511, answer score: 9
Revisions (0)
No revisions yet.