Differences between the GroupBy sorting principles of MySQL and SQLLite

Differences between the GroupBy sorting principles of MySQL and SQLLite

When we perform group by on the records of a table and do not explicitly use aggregate functions such as sum, min, and max, the group by sorting rules are as follows:

As we all know, when using group by, the database traverses all records in the database table for matching.

The following SQL statement is used to query a message of the latest time in the same groupid record in the table. The statement differences between mysql and sqllite are given:

MYSQL statement

SELECT * FROM(SELECT t.id,t.data_id,t.send_username,t.recv_username,t.message,IFNULL(NULLIF(t.groupid,''),t.recv_username) AS groupid,t.`created_date`FROM tb_push_data t WHERE STATUS = 0 ORDER BY t.`created_date` desc,t.id desc) t1GROUP BY t1.groupidORDER BY t1.`created_date` DESC,t1.id DESC

SQLLite statement

select * from(select t.id,t.data_id,t.login_username,t.send_username,t.recv_username,t.message,ifnull(nullif(t.groupid,''),t.send_username) as groupid,t.timefrom tb_recved_data t where status = 0 order by t.time asc, t.id asc) t1group by t1.groupidorder by t1.time desc,t1.id desc

When comparing SQL statements, we can find that ASC is used in MYSQL in the ORDER BY statement at the end of the subquery, and DESC is used in SQLLite.

This is because MYSQL ignores repeated group fields when traversing table records. SQLLite is covered.

Therefore, desc is used in mysql to obtain a record sent at the latest, and asc is used in sqllite.