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');