Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the difference between mysql group by and order by". In the operation of actual cases, many people will encounter such a dilemma. Then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The code is as follows:

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 1415 37')

(2, 'bbb', 2,' 2010-06-10 19-14-55')

(3, 'ccc', 1,' 2010-06-10 19 16purl 02')

(4, 'ddd', 1,' 2010-06-10 19Suzhou 1615')

(5, 'eee', 2,' 2010-06-10 19rig 16purl 35')

I now need to take out the latest content in each category.

The copy code is as follows:

Select * from test group by category_id order by `date`

The results are as follows

Obviously. This is not the data I want, because the order in which msyql has been executed is

Quote

The order of writing: select. From... Where.... Group by... Having... Order by..

Execution order: from... Where...group by... Having.... Select... Order by...

Therefore, it is the final result of grouping in the results obtained by order by.

The result from from to where is as follows.

When I arrived at group by, I got a number of groups based on category_id.

When it comes to select, just take the first message from each of the above groups and the result is as follows

Even order by only sorts from the above results. It is not the latest information for each category.

Back to my purpose-- the latest information in the category

According to the above analysis, when group by goes to select, it only fetches the first piece of information in the packet. There are two solutions.

1 where and where group by (sort the groups)

2. Tampering with the data returned from form (that is, using subqueries)

The solution by where+group by

The function to sort the groups in group by I only found that group_concat () can sort, but the function of group_concat is to concatenate the values in the fields in the group.

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

Improve it 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 solution

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

This is the end of the content of "the difference between mysql group by and order by". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.