How to select the line with the max(date)? [duplicate]

Question

the data looks as following:

id group date
1  a     2013-01-01
1  b     2014-01-01
2  a     2012-01-01
2  b     2013-01-01

My database is mysql, for each id, I need to select the line with max(date), I wrote the SQL as following:

select id,group, max(date)
from table1
group by id;

But it does not work well.

Answer 1

SELECT B.* FROM
(
    select id,max(date) date
    from table1 group by id
) A INNER JOIN table1 B USING (id,date);

You should create this index to help this run faster

ALTER TABLE table1 ADD INDEX (id,date);

Sample Data Loaded

mysql> use test
Database changed
mysql> drop table if exists table1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table table1
    -> (id int not null,
    -> `group` varchar(10) not null,
    -> date date,
    -> key (id,date)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into table1 (id,`group`,date) values
    -> (1,'a','2013-01-01'),
    -> (1,'b','2014-01-01'),
    -> (2,'a','2012-01-01'),
    -> (2,'b','2013-01-01');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from table1;
+----+-------+------------+
| id | group | date       |
+----+-------+------------+
|  1 | a     | 2013-01-01 |
|  1 | b     | 2014-01-01 |
|  2 | a     | 2012-01-01 |
|  2 | b     | 2013-01-01 |
+----+-------+------------+
4 rows in set (0.00 sec)

mysql>

Here is the query executed

mysql> SELECT B.* FROM
    -> (
    ->     select id,max(date) date
    ->     from table1 group by id
    -> ) A INNER JOIN table1 B USING (id,date);
+----+-------+------------+
| id | group | date       |
+----+-------+------------+
|  1 | b     | 2014-01-01 |
|  2 | b     | 2013-01-01 |
+----+-------+------------+
2 rows in set (0.00 sec)

mysql>

Give it a Try !!!