Shulou(Shulou.com)05/31 Report--

This article mainly introduces SQL how to achieve the maximum ranking within the group, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to understand it.

Test cases-- build a table create table testorder (id int not null,no int not null,name char (10) not null,primary key (id)) engine=innodb;-- to write data insert into testorder values (1pje 1pje mike'), (2pje 2pje John'), (3pje 3pr Herry`), (4pas 4 mikes), (5pas 5 mikes'), (6pje 1pr John'), (7pr 2pr John'), (8p1mil mike'), (9je 1m mike');-- query 1select * from testorder +-id | no | name | 1 | 1 | Mike | 2 | John | 3 | 3 | wyett | 4 | 4 | Herry | 5 | Mike | | 6 | 1 | John | 7 | John | 8 | 1 | Mike | 9 | Mike | +-query 2select * from testorder order by no desc +-id | no | name | 5 | Mike | 4 | Herry | 3 | 3 | wyett | 2 | 2 | John | 7 | John | | 1 | 1 | Mike | 6 | John | 8 | Mike | 9 | Mike | +-query 3 select * from (select id,no Name from testorder order by no desc) a group by a.name

The SQL of query 3 is what we need to discuss, and it is also the SQL used by the business line to achieve the maximum ranking within the group. Standard programmer feedback question way: XXX query before the point in time is normal, and then suddenly abnormal, has your DBA made any changes? I restored the data to my own test machine, and the return value was normal. Regardless of whether the posture is correct or not, the analysis of this SQL, we can actually see: (1) programmers expect the results of group by execution to take values according to the data order of temporary table a; (2) programmers do not consider version factors, the amount of data changes; to this end, I built the above test cases.

test

Testing in different versions of MySQL: it is found that when Percona 5.5 Percona 5.1 MySQL 5.6 closes sql_mode= ONLY_FULL_GROUP_BY,MySQL5.1 and other versions, the returned values are in the order expected by programmers, and the same name returns the data with the highest no value in the order of order by no desc.

+-4 | Herry | 2 | John | 5 | Mike | 3 | wyett | +-+

In mysql5.7, when the sql_mode= ONLY_FULL_GROUP_BY and mariadb 10.* versions are turned off, the same name value is returned, while the earliest written data row is taken, order by no desc is ignored, and the data is returned in the logical storage order.

+-4 | Herry | 2 | 2 | John | 1 | 1 | Mike | 3 | wyett | +-+-- +--

In fact, here, SQL is equivalent to select id,no,name from testorder group by name.

Here we can see that the return values of different versions are different, and shelving the change in the amount of data leads to different discussions about the execution results, because the size of the data is difficult to test.

Official document

For the above test results, in the official documentation, there are the following references

If ONLY_FULL_GROUP_BY is disabled...In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate, which is probably not what you want. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Result set sorting occurs after values have been chosen, and ORDER BY does not affect which value within each group the server chooses.

ONLY_FULL_GROUP_BY, the SQL_MODE, was introduced when it came out of mysql5.6 (mariadb 10.0), but the content discussed in this article has nothing to do with it. You can check the documentation for yourself, and we won't discuss it here. In 5.6, the official document of 5.5 has the same content, and Mariadb has a similar interpretation.

If you select a non-grouped column or a value computed from a non-grouped column, it is undefined which row the returned value is taken from. This is not permitted if the ONLY_FULL_GROUP_BY SQL_MODE is used.

Moreover, the order by in the subquery subtable after from is also explained.

A query such asSELECT field1, field2 FROM (SELECT field1, field2 FROM table1 ORDER BY field2) aliasreturns a result set that is not 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 in the FROM clause) do not come in any specific order.

Well, with these explanations, the question is clear:

Order by in subquery after from will be ignored

The rows returned by group by cloumn are unordered

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

Solution.

So how to solve this problem?

There are some SQL on the Internet, which obviously do not meet the demand. Here is a demonstration, hoping that students will not be misled:

Error SQL collection

Select id,sbustring (GROUP_CONCAT (distinct no order by no desc separator'),'', 1), name from testorder group by name;-- affects the order of returned result sets alter table testorder add index idx_no_name (no desc, name) by adding indexes;-- it turns out that even so, desc will not be executed correctly Select * from (select id,no,name from testorder order by no desc) a group by a.nameselect id,max (no), name from testorder group by name

We can write like this, even though it's not efficient.

Select a. ID. No. Name from testorder an inner join (select max (no) no,name from testorder group by name) b on a.no=b.no and a.name=b.name group by name,no

Or this.

(select max (no) from testorder where name=a.name) Thank you for reading this article carefully. I hope the article "how to achieve the maximum ranking of SQL within the group" shared by the editor will be helpful to you. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.