Author: Liu Chen

BISAL, with more than 10 years of experience in application operation and maintenance, currently mainly engaged in database application research and development ability improvement work, Oracle ACE, with Oracle OCM & OCP, Exin DevOps Master, SCJP and other international certifications, The first member of Oracle YEP in China, OCMU member, one of the translators of DevOps best practices in Chinese, expert blogger of CSDN & ITPUB, public account “Personal Desselshop of BISAL”, has been sharing technical articles for a long time, and shared technical topics online and offline for many times.

Source of this article: original contribution

* Produced by the open source community of ecoson, the original content is not allowed to be used without authorization, please contact the small edition and indicate the source.

Two days ago, my colleague had a request for MySQL data grouping. The following test data is needed to find out the most recent record of create_date in each name group:

Note that you are using MySQL 5.6, and that you originally used this statement:

select name, value, create_date, update_date from t1 group by name order by create_date desc;

Name =a; create_date = value=3; name=d; create_date = value=10;

All you get with this SQL is the first record inserted in each name group, then sorted in descending order by create_date, which is completely different from the original requirement.

At this point, divide and conquer strategy can be adopted, sorting first, and then grouping:

select * from (select name, value, create_date, update_date from t1 order by create_date desc) t group by t.name;

The data of the original requirement can be obtained:

Of course, for this need, there may be other methods, interested friends, you can try to write, share.

SELECT * FROM ‘GROUP BY’ SELECT FROM ‘GROUP BY’ SELECT FROM ‘GROUP BY’ SELECT FROM ‘GROUP BY’ SELECT FROM ‘GROUP BY’ SELECT FROM ‘GROUP BY’ SELECT FROM ‘GROUP BY’ SELECT FROM ‘GROUP BY’ SELECT FROM ‘GROUP BY’ SELECT FROM ‘GROUP BY’ SELECT FROM ‘GROUP BY’ SELECT FROM ‘GROUP BY’ SELECT FROM ‘GROUP BY’ SELECT FROM ‘GROUP BY’ SELECT FROM ‘GROUP BY’

If we execute the same statement in MySQL 5.7:

select name, value, create_date, update_date from t1 group by name order by create_date desc; 

This will prompt this error:

If you are in Oracle, you will be prompted with this:

Is this a MySQL 5.6 feature? The sql_mode parameter in this 5.6 environment is as follows:

In 5.7, the SQL_MODE parameter is as follows, with the addition of ONLY_FULL_GROUP_BY, which means “columns that appear in SELECT statements, HAVING conditions, and ORDER BY statements.” Must be a GROUP BY column or a function column that depends on the GROUP BY column. This explains why the phenomenon is different between 5.6 and 5.7, or if 5.7 requires a higher standard of SQL format validation BY default:

Therefore, from 5.6 to 5.7, it is likely that the same SQL will be executed differently, which will require a high level of compatibility testing due to both features and different configuration parameters.

Can delete this in 5.7 sql_mode ONLY_FULL_GROUP_BY, can achieve the same effect, 5.6 or rewrite the SQL, such as:

select * from t1 a where create_date = (select max(create_date) from t1 b where a.name = b.name);

Or,

select * from t1 a where not exists (select * from t1 b where a.name = b.name and b.create_date > a.create_date);

MySQL 8.0 supports the ROW_NUMBER () function, which should operate as Oracle does below.

Oracle implements this requirement using row_number() : row_number()

select * from (select name, create_date, row_number() over (partition by name order by create_date desc) as r from t1) where r=1;