MySQL GROUP by and order by data ordering problem

Source: Internet
Author: User
Tags mysql manual

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
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.