Mysql group by sorting

For example, if there is a post reply table, posts (id, tid, subject, message, dateline ),

Id is the auto-increment field, tid is the id (foreign key Association) of the reply topic post, subject is the reply title, message is the reply content, dateline is the reply time, expressed in UNIX timestamp,

Now we need to select the first 10 latest replies from different topics.


SELECT * FROM posts group by tid LIMIT 10


Such an SQL statement is not the latest reply you want, but the earliest reply. It is actually the first reply record of a topic!

That is to say, the group by statement is not sorted. How can we sort the GROUP in descending order of dateline? Add the order by clause?

See the following:

SELECT * FROM posts group by tid order by dateline desc limit 10


The results selected by this statement are exactly the same as those above, but the results are sorted in reverse order, and each selected record is still the above record, the reason is that group by is executed before order by, so there is no way to sort group by before, that is, before grouping. Some netizens will write the following SQL statement:


SELECT * FROM posts group by tid desc order by dateline desc limit 10


That is to say, add the descending order after the tid field of group by, so that we can get the final reply when grouping? The execution result of this statement will be exactly the same as above. Adding DESC and ASC here will not affect the execution result! In fact, this is an incorrect statement, because group by has no sorting function before. In the mysql manual, the group by statements are sorted in a certain order. What is the order in a certain order? In fact, there is no order at all, because tid is grouped by tid. In fact, tid is classified into a group equal. In this case, group by tid DESC can be considered to be arranged in reverse order according to tid when grouping BY tid. Isn't that true? Since it is grouped BY tid, of course tid is equal to the same GROUP, at this time, there is a P in ascending order according to the tid flashback!


Some netizens invented the following statement:


SELECT * FROM posts group by tid, dateline desc order by dateline desc limit 10


In this way, I can sort data by dateline in reverse order before grouping. In fact, this statement does not apply to tid grouping, because it is still the preceding statement, adding desc or asc after the group by field is incorrect. The netizen intends to group by tid and sort the dateline in reverse order when grouping! Actually, this sentence is equivalent to the following statement: (remove DESC after the group by field)


SELECT * FROM posts group by tid, dateline order by dateline desc limit 10


That is to say, according to the tid and dateline grouping, only when the record tid and dateline are equal at the same time can be summarized into a group, which is obviously impossible, because the dateline timeline is basically unique!


Someone wrote the following statement:


SELECT *, max (dateline) as max_line FROM posts group by tid order by dateline desc limit 10


This statement selects the maximum release time, but you can compare the difference between dateline and max_dateline! (It may be equivalent that only one target record is recorded in the group !)


Why? The reason is very simple. This statement is equivalent to selecting the maximum release time of the group after group! Does not affect the group! Because the SELECT clause is last executed!

Later, some netizens invented the following statement!


SELECT *, max (dateline) as max_line FROM posts group by tid HAVING dateline = max (dateline)


Order by dateline desc limit 10


The expected results of this statement are not the same as you think! Because you will find that a large number of records are missing in the grouping results! Why? Because HAVING is executed in a group, that is, when grouping, add the following condition: the selected dateline must be equal to the maximum dateline of the group, the execution result 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


Have you understood this SQL statement?

Dateline = max (dateline) is valid only when there is only one record in the group. The reason is clear! Only one item will be equal to the maximum release time of the group (the default dateline is a non-repeated value)


The original reason is that group by does not have the sorting function. All these sorting functions are just an illusion. Therefore, the final dateline and max (dateline) cannot be equal, unless there is only one record in this group! When grouping, group by may come one BY one and find the same tid, remove it, and keep the record of the first discovery, therefore, the records are always listed in the default index order!


So many times, is there a way to make group by the Pre-execution group? Yes, subquery!


Simplest:


SELECT * FROM (SELECT * FROM posts order by dateline DESC) group by tid order by dateline desc limit 10


Some netizens also use self-connection to achieve higher efficiency than the preceding subquery. However, to be simple and clear, only such a method is used. group by has no sorting function, maybe it's something that mysql is mentally retarded, maybe it's something I haven't found,

Looking forward to making great strides!