Differences between Group By sorting principles of MySQL and SQLLite

Differences between Group By 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
) T1
Group by t1.groupid
Order 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. time
From tb_recved_data t where status = 0 order by t. time asc, t. id asc
) T1
Group by t1.groupid
Order 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.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: