About MySQL group by grouping by time maximum value implementation Method!

class If there is a reply table for a post, posts (ID, TID, subject, message, Dateline), ID for the auto-grow field, tid for the reply's subject post ID (Foreign key Association), subject for reply title, ME Ssage to reply to the content, Dateline for the reply time, with the Unix timestamp, now ask to select the top 10 different topics from the latest replySELECT *  fromPostsGROUP  byTid LIMITTensuch an SQL statement is not the latest reply you want, but the earliest reply, which is actually the first reply record of a topic! Which meansGROUP  byStatement is not sorted, how can you makeGROUPIn reverse order of Dateline? PlusOrder  byclause? See below:SELECT *  fromPostsGROUP  byTidORDER  byDatelineDESCLIMITTenThe 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, becauseGroup  bywill be better thanOrder  byFirst, so there's no way toGroup  bybefore, that is, in the group before the sorting, a netizen will write the following SQL statement:SELECT *  fromPostsGROUP  byTidDESC ORDER  byDatelineDESCLIMITTenin other words ,GROUP  byThe fields of the Tid are followed by a descending order, so that the last reply to the group is not available? The result of this statement will be exactly the same as above, plusDESCand ASC have no effect on execution results! In fact, this is a wrong statement because the group byBefore there was no sorting function, the MySQL manual said,GROUP  byare 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 the induction of a group, so to think,GROUP  byTidDESCcan be considered in accordance with the TID group, in accordance with the reverse order of the TID, this is not the case, since it is in accordance with the TID grouping, of course, the TID is equal to a group, and this time according to Tid flashback or ascending has a P use! So some netizens invented the following statement:SELECT *  fromPostsGROUP  byTid, DatelineDESC ORDER  byDatelineDESCLIMITTenthought 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 the above, in the group byfield after addingdescStill isASCIs the wrong way of writing, and this user intent is to follow the TID group, and in the group in accordance with the Dateline in reverse order! And the actual sentence is equivalent to the following wording: (removeGROUP  byAfter the fieldDESC)SELECT *  fromPostsGROUP  byTid, DatelineORDER  byDatelineDESCLIMITTenIn 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) asMax_line fromPostsGROUP  byTidORDER  byDatelineDESCLIMITTenThis 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!) ) for what? The reason is very simple, this statement is equivalent to the group bySelect the maximum release time for this group later! No effect on grouping! Because the SELECT clause is the last execution! Later, more netizens invented the following wording! SELECT *,Max(Dateline) asMax_line fromPostsGROUP  byTid havingDateline=Max(Dateline)ORDER  byDatelineDESCLIMITTenThe 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 havingis executed at the time of grouping, also said: In the grouping time adds one such condition: chooses the dateline to be equal with this group biggest dateline, executes the result and the following statement the same:SELECT *,Max(Dateline) asMax_line fromPostsGROUP  byTid having Count(*)=1ORDER  byDatelineDESCLIMITTendid 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 not a duplicate value) or becauseGroup  byThere is no sorting 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  byat the time of grouping, may be one to look for, found that there is an equal Tid, remove, keep the first discovery of the record, so the found records are always only in the default index order! So much so that there is no way to letGroup  bybefore the execution of the group AH? Yes, sub-query Ah! The simplest:SELECT *  from(SELECT *  fromPostsORDER  byDatelineDESC)GROUP  byTidORDER  byDatelineDESCLIMITTenThere are netizens using self-connection to achieve, such efficiency should be higher than the above sub-query efficiency, but, in order to be simple and clear, only use such a kind of,GROUPby no sorting function, may be MySQL mentally retarded place, perhaps I have not found, look forward to shoot bricks! The above: http://Www.alixixi.com/Program/A/2011012867346shtml I have solved the problem according to the author's idea, thank the author very much! But there's one more thing to note,SELECT *  from(SELECT *  fromPostsORDER  byDatelineDESCafter this code to the query for the individual name, table name use, or error! Exactly the right wording:SELECT *  from(SELECT *  fromPostsORDER  byDatelineDESC) biaomingGROUP  byTidORDER  byDatelineDESCLIMITTen 

About MySQL group by grouping by time maximum value implementation Method!