The difference between the group by sort principle of MySQL and sqllite

When we group by on a table record, when aggregate functions such as SUM, Min, max are not explicitly used, the group by collation is compared with MySQL and sqllite

As you all know, when group by, the database is matched to all records that traverse the database table.

The following SQL is intended to query a message in the table GroupID the same record, with the latest time, giving the statement differences between MySQL and Sqllite:

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) T1grou P by T1.groupidorder by T1. ' Created_date ' desc,t1.id DESC

Sqllite statements

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) T1GR OUP by T1.groupidorder by T1.time desc,t1.id desc

Note that compared to SQL, you can find that the subquery uses ASC in the last order by, while in Sqllite we use DESC

The reason for this is that MySQL is ignored when it iterates through the table records when it discovers duplicate group fields. And the sqllite is covered.

So we're using ASC in MySQL to get a record of the latest send, using Desc,sqllite