Mysql sql statement in the group by and order by comparison

I now need to remove the most recent content from each category

The code is as follows:
select * from test group by category_id order by `date`

The result is as follows

obvious. This is not the data I want, because msyql has the order of execution

Quote

Write the order: select ... from ... where .... group by ... having ... order by ..
Execution order: from ... where ... group by ... having .... select ... order by ...

So in the order by the results obtained is the end of the group finished.
From to where the results as follows.

When group by, you get multiple groups grouped by category_id

When it comes to select, just take the first message from each of the above groups. The result will be as follows

Even order by is only sorted from the result above. Not the latest information for each category.
Back to my purpose - the latest information in the category According to the above analysis, group by to select only to the first packet in the group. There are two solutions
1, where + group by (to sort the group)
2, the data returned from the form of hands and feet (that is, sub-query)

By the group where the solution of the group by the group by sorting function I only found that the group_concat () can be sorted, but the role of group_concat is to group the values ​​?? in the field together.

select group_concat (id order by `date` desc) from` test` group by category_id

Improve again

select * from `test` where id in (select substring_index (group_concat (id order by` date` desc), ',', 1) from `test` group by category_id) order by` date` desc

Subquery solutions

select * from (select * from `test` order by` date` desc) `temp` group by category_id order by` date` desc

Data table structure

create table `test` (
`id` int (10) not null auto_increment,
`name` varchar (255) not null,
`category_id` int (10) not null,
`date` timestamp not null default current_timestamp,
primary key (`id`)
)
engine = myisam
row_format = default;
insert into `test` (` id`, `name`,` category_id`, `date`)
values
(1, 'aaa', 1, '2010-06-10 19:14:37'),
(2, 'bbb', 2, '2010-06-10 19:14:55'),
(3, 'ccc', 1, '2010-06-10 19:16:02'),
(4, 'ddd', 1, '2010-06-10 19:16:15'),
(5, 'eee', 2, '2010-06-10 19:16:35');