MySQL group within the sorting to take the maximum value

Recent business Feedback A query exception problem requires the DBA to interpret the query result exceptions and help them resolve the problem. The problem essence is the problem of sorting the maximum within a group, and I built the test case according to the business requirements

Test Cases

--Create TABLE Testorder (id int not null,no int not null,name char (TEN) not null,primary key (ID)) engine=innodb;--write number According to insert into Testorder values ("Mike"), (2,2, ' John '), (3,3, ' Wyett '), (+, ' Herry '), (5,5, ' Mike '), (6,1, ' John '), (7,2 , ' John '), (8,1, ' Mike '), (9,1, ' Mike ');--Query 1select * FROM testorder;+----+----+-------+| ID | No |  Name |+----+----+-------+|  1 | 1 |  Mike | |  2 | 2 |  John | |  3 | 3 |  Wyett | |  4 | 4 |  Herry | |  5 | 5 |  Mike | |  6 | 1 |  John | |  7 | 2 |  John | |  8 | 1 |  Mike | |  9 | 1 | Mike |+----+----+-------+--query 2select * from Testorder ORDER BY no desc;+----+----+-------+| ID | No |  Name |+----+----+-------+|  5 | 5 |  Mike | |  4 | 4 |  Herry | |  3 | 3 |  Wyett | |  2 | 2 |  John | |  7 | 2 |  John | |  1 | 1 |  Mike | |  6 | 1 |  John | |  8 | 1 |  Mike | |  9 | 1 | Mike |+----+----+-------+--Query 3select * FROM (select Id,no,name from Testorder ORDER BY no DESC) a group by a.name; 

Query 3 This SQL is what we need to discuss and also the SQL used by the line of business to achieve the maximum value for the sort within the group. Standard Programmer feedback problem mode: XXX point in time before the normal query, which suddenly is not normal, your DBA did what changes? I restored the data to my test machine, and the return value was normal. For the moment, whether the posture is correct, the analysis of this SQL, we can actually see: (1) The programmer expects that the group by execution result is based on the data order of temporary Table A, (2) The programmer does not consider the version factor, data volume changes factors; To do this, I built the test cases above.

Test

In different versions of MySQL to test: found in Percona 5.5,percona 5.1,mysql 5.6 Close sql_mode= only_full_group_by, MySQL5.1 and other versions, the return value is exactly as the programmer expects the order, according to order by no DESC, the same name returns the maximum value of the data;

+----+----+-------+| ID | No | Name  |+----+----+-------+|  4 |  4 | Herry | |  2 |  2 | John  | |  5 |  5 | Mike  | |  3 |  3 | Wyett |+----+----+-------+

In mysql5.7, closing the sql_mode= only_full_group_by and mariadb 10.* versions, the same name value is returned by fetching the first written data row, ignoring the order by no DESC, which is returned in the logical storage order of the data ；

+----+----+-------+| ID | No | Name  |+----+----+-------+|  4 |  4 | Herry | |  2 |  2 | John  | |  1 |  1 | Mike  | |  3 |  3 | Wyett |+----+----+-------+

In fact, here, SQL is equivalent to select Id,no,name from Testorder group by name.
Here we see different versions of the return value is different, first put the data amount of changes caused by different results of the discussion, because the size of the data volume is difficult to test.

Official Documents

For the above test results, in the official document, like the following reference

If only_full_group_by is disabled ... In this case, the server was free to choose all value from each group, so unless they was the same, the values chosen are I Ndeterminate, which is probably don't what do you want. Furthermore, the selection of values from each group cannot is influenced by adding a ORDER by clause. Result set sorting occurs after values has been chosen, and ORDER by does not affect which value within each group the SE RVer chooses.

Only_full_group_by This sql_mode is introduced in mysql5.6 (MARIADB 10.0), but the content discussed in this article is not related to it, you can view the document yourself, there is no discussion here. The official documents in 5.6,5.5 have the same content, and mariadb have similar explanations.

If you select a non-grouped column or a value computed from a non-grouped column, it is undefined which row the returned V Alue is taken from. This isn't permitted if the only_full_group_by Sql_mode is used.

Also, an order by IS explained in the Subquery sub-table after the from.

A query such asselect field1, field2 from (SELECT field1, field2 from table1 ORDER by Field2) aliasreturns a result set That isn't necessarily ordered by Field2. This is not a bug. A "Table" (and subquery in the FROM clause too) is-according to the SQL Standard-an unordered set of rows. Rows in a table (or in a subquery with the FROM clause) does not come on any specific order.

Well, with these explanations, the question is clear:

• ORDER BY in subquery after from is ignored
• The rows returned by the group by Cloumn are unordered

Therefore, the correct return value obtained by the business is also a mistake.

Solutions

So how do you solve this problem?

There are some SQL on the Internet, obviously do not meet the needs, here to do a demonstration, I hope students avoid being misled:

Error SQL Collection

Select Id,sbustring (Group_concat (distinct no ORDER BY no desc separator "),", 1), name from Testorder GROUP by name;

---by adding an index to affect the returned result set order ALTER TABLE Testorder Add index idx_no_name (no desc, name);--the result proves that even so, DESC will not be executed correctly;

--Our Programmer's wording select * FROM (select Id,no,name from Testorder ORDER BY no DESC) a GROUP by a.name

Select Id,max (No), name from Testorder Group by name

We can write this, though not very efficient.

Select A.id,a.no,a.name from Testorder a inner join (select MAX (NO) no,name from             testorder     Group by name) B on A.N O=b.no and A.name=b.name GROUP by Name,no

Or so

Select A.id,a.no,a.name from Testorder a group by a.name,a.no have a.no= (select Max (no) from Testorder where name=a.name )

MySQL group within the sorting to take the maximum value