How to select the line with the max(date)? 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.
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&gt; use test
Database changed
mysql&gt; drop table if exists table1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

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

mysql&gt; 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&gt;


Here is the query executed

mysql&gt; SELECT B.* FROM
    -&gt; (
    -&gt;     select id,max(date) date
    -&gt;     from table1 group by id
    -&gt; ) 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&gt;


Give it a Try !!!