MySQL GROUP by and order by data ordering problem

There are two ways to solve this problem

1,where+group by (sort the group)
2, the data returned from the form of the hands and feet (that is, subqueries)
The solution by the Where+group by
A function that sorts the groups in group by I only find Group_concat () can sort, but Group_concat's role is to concatenate the values in the fields in the group.

The code is as follows	Copy Code
Select Group_concat (ID order by ' date ' desc) from ' Test ' GROUP by category_id

Just a little bit better.

The code is as follows	Copy Code
SELECT * from ' test ' where ID in (select Substring_index (Group_concat (ID order by ' date ' desc), ', ', ', 1) ' Test ' GROUP by category_id) Order BY ' date ' desc

Subquery Solution

The code is as follows	Copy Code
SELECT * FROM (SELECT * "Test ' ORDER by '" Date ' desc ') ' temp ' GROUP by category_id Order by ' date ' desc

own example

The code is as follows	Copy Code
SELECT * From ( SELECT * From ' Ecm_copy_goods_change ' ORDER BY ' CID ' DESC ) ' Ecm_copy_goods_change ' GROUP by goods_id ORDER BY ' CID ' DESC

One DZ Forum Use a case study of the final release content

When using MySQL to sort with the idea of grouping in descending order to get a set of data, and the use of orders by often get not ideal results, then how to use Group by and order by to get the desired data results?

For example, there is a reply form for a post, posts (ID, TID, subject, message, Dateline),

ID for the automatic growth field, Tid for the reply to the topic of the ID (Foreign key Association), subject for reply to the title, message for the reply, Dateline for reply time, with a Unix timestamp,

Now ask for the top 10 new responses from different topics

The code is as follows	Copy Code
SELECT * from posts GROUP by Tid LIMIT 10

Such an SQL statement is not the most recent reply you want, but the earliest reply, which is actually the first reply record of a topic!

That is, the group by statement is not sorted, so how do we get the group to follow Dateline in reverse order? Add an ORDER BY clause?

Look below:

The code is as follows	Copy Code
SELECT * from posts GROUP by Tid order by Dateline DESC LIMIT 10

The result of this statement is exactly the same as the one above, however, the results are sorted in reverse order, and each selected record is still the record above, because the group by IS executed before the order by, so there is no way to sort the group by before, that is, before grouping. A netizen will write the following SQL statement:

The code is as follows	Copy Code
SELECT * from posts GROUP by Tid DESC order by Dateline DESC LIMIT 10

That is, after the field Tid of group BY, add the descending order, so that you can not get the final reply to the group? The execution of this statement will be exactly the same as the above, plus DESC and ASC have no effect on the execution result! In fact, this is a bad statement, because the group by before the sorting function, the MySQL manual says, GROUP by is sorted in some order, in what order in the end? In fact, there is no order, because in accordance with TID group, in fact, that is to say, the TID equal to a group, if so, group by Tid DESC can be considered in accordance with the TID group, according to the TID in reverse order, this does not pull, since it is in accordance with the TID grouped Of course it's tid equal to a group, and there's a p in the TID flashback or ascending order!

Some netizens invent the following statement:

The code is as follows	Copy Code
SELECT * from posts GROUP by Tid, Dateline DESC ORDER by Dateline DESC LIMIT 10

I thought that way I could arrange the dateline in reverse order before grouping, in fact, this statement does not play in accordance with the role of Tid group, the reason is above, in the group by the field plus DESC or ASC is the wrong way to write, and this way the user is intended to be grouped according to Tid, and in the group In accordance with the Dateline row in reverse order! The actual sentence is equivalent to the following: (Remove the DESC after the GROUP by field)

The code is as follows	Copy Code
SELECT * from posts GROUP by Tid, Dateline order by Dateline DESC LIMIT 10

That is, by combining TID and Dateline, it is not possible to generalize to a group only when the records TID and Dateline are equal, because the Dateline timeline is basically unique!

Someone writes the following statement:

The code is as follows	Copy Code
SELECT *,max (Dateline) as Max_line from posts GROUP by Tid order by Dateline DESC LIMIT 10

This statement is correct to select the maximum release time, but you can compare Dateline and max_dateline is not equal! (There may be quite a situation where the target record for the group is only one time!) )

Why, then? The reason is simple, this statement is the equivalent of the group by the time to select the largest release times! Does not have any effect on the grouping! Because the SELECT clause is last executed!

Later, even more netizens invented the following wording!

The code is as follows	Copy Code
SELECT *,max (Dateline) as Max_line from posts GROUP by Tid have Dateline=max (Dateline) Order by Dateline DESC LIMIT 10

The expected result of this statement is not the same as the imagined one! Because you will find that a large number of records in the grouped results are gone! Why? Because having is performed when grouping, it is said that: when grouping, add one such condition: The selected dateline to be equal to the largest dateline of this group, the result of execution is the same as the following statement:

The code is as follows	Copy Code
SELECT *,max (Dateline) as Max_line from posts GROUP by TID have count (*) =1 ORDER by Dateline DESC 10

Did you see the SQL statement?

Dateline=max (Dateline) only when the records in the group only one time to set up, the reason is clear! Only one of them will be the same as the maximum publication time of this group, (default dateline is not duplicate value)

Because group by does not have a sort function, all of these sort functions are just illusions, so your final dateline and Max (Dateline) will never be equal unless there is only one record in this group! Group by in the group, may be a one to find, found that there are equal tid, remove, keep the first found that the record, so find out the record is always in accordance with the default index order!

So so much, is there a way to let group by execution before the group AH? Yes, subquery!

The simplest:

code is as follows	copy code
select * FROM ( SELECT * FROM posts-dateline DESC) GROUP by Tid order by Dateline DESC