The group by and order by mixed results in MySQL are not ideal results

When using MySQL sorting, you would expect to get a set of data in descending order, but using order by often gets not the desired result, so how can you use Group by and order by to get the desired data results?

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

ID is the autogrow field, TID is the ID of the topic post for that reply (Foreign Key Association), subject is the reply title, message is reply content, Dateline is reply time, and is represented by the Unix timestamp,

Now ask for the top 10 latest responses from different topics

SELECT * from posts GROUP by Tid LIMIT

Such an SQL statement is not the latest 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 can we let group in reverse order dateline? Plus the ORDER BY clause?

See below:

SELECT * from posts GROUP by Tid ORDER by Dateline DESC LIMIT 10

The result of this statement is exactly the same as above, but the results are sorted in reverse order, and each record selected is still the record above, because 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:

SELECT * from posts GROUP by tid desc ORDER by dateline desc LIMIT

That is, the group by field after the TID is followed by a descending order, so that the group can not get the final reply? The result of this statement will be exactly the same as above, with DESC and ASC not having any effect on the execution result! This is actually a wrong statement because the group by does not have a sort function, and the MySQL manual says that group by is sorted in some order, in what order? In fact, there is no order, because in accordance with the TID group, in fact, that is, the TID equal to a group, so to think, the group by Tid DESC can be considered in accordance with the TID group, in accordance with the order of the TID, this is not to say, since it is grouped according to the TID, Of course, the TID is equal to a group, and this time according to the TID flashback or ascending has a P use!

So some netizens invented the following statement:

SELECT * from posts GROUP by Tid, Dateline desc ORDER by dateline desc LIMIT

Thought so I can in the group before the Dateline in reverse order, in fact, this statement does not play in accordance with the role of the TID group, the reason or above, in the Group by field after the addition of DESC or ASC is the wrong way of writing, and this type of user intent is to follow the TID group, and in the sub- Group in reverse order according to the Dateline row! The actual sentence is equivalent to the following: (Remove DESC after the GROUP by field)

SELECT * from posts GROUP by Tid, Dateline ORDER by Dateline DESC LIMIT

In other words, according to the TID and Dateline joint grouping, only when the record tid and the Dateline equal time is summed up in a group, this is obviously not possible, because the Dateline timeline is basically unique!

Someone writes the following statement:

SELECT *,max (Dateline) as Max_line from posts GROUP by Tid ORDER by Dateline DESC LIMIT

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

Why is it? The reason is simple, this statement is equivalent to the group by the selection of the largest release time! No effect on grouping! Because the SELECT clause is the last execution!

Later, more netizens invented the following wording!

SELECT *,max (Dateline) as Max_line from posts GROUP by Tid have Dateline=max (Dateline) ORDER by Dateline DESC LIMIT Ten

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 results of the grouping are gone! Why? Because having is carried out at the time of grouping, also say: In the time of grouping, add one such condition: Choose out dateline to be equal to the biggest dateline of this group, the result of execution is same as the following statement:

SELECT *,max (Dateline) as Max_line from posts GROUP by TID have count (*) =1 ORDER by Dateline DESC LIMIT

Did you see this SQL statement?

Dateline=max (Dateline) only when there is only one record in the group, the reason is clear! Only one of them will be equal to the maximum release time for this group, (default dateline is a non-repeating value)

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

So much so, is there a way for group by to execute the former group? Yes, sub-query Ah!

The simplest:

SELECT * FROM [select * from Posts ORDER by Dateline Desc] as Testbiao GROUP by tid order by dateline Desc LIMIT 10< /c0>