Mysql GROUP By Sort problem

Class if 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


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:

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 arranged in reverse order, and each selected record is still above the record, because the group by will be more than the order by the first execution, so there is no way to group by before, that is, before grouping to sort, there are users will Write down the following SQL statement:


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!


So some netizens invent the following statement:


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 Group the time in accordance with Dateline row in reverse order! The actual sentence is equivalent to the following: (Remove the DESC after the GROUP by field)


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:


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!


SELECT *,max (Dateline) as Max_line from posts GROUP by Tid has 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:


SELECT *,max (Dateline) as Max_line from posts GROUP by Tid having Count (*) =1


ORDER BY Dateline DESC LIMIT 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:


SELECT * FROM (SELECT * from posts-dateline DESC) GROUP by Tid order by Dateline DESC LIMIT 10


Also has the Netizen to use the connection realization, such efficiency should be higher than the above subquery efficiency, however, in order to be simple and clear, only then uses this kind of, the GROUP by does not have the sorting function, may be the MySQL retarded place, perhaps is I have not discovered,

Look forward to the high man shot bricks!