SQLite Forum

SQLite Group By results order behaviour change 3.28.0 onwards
Login

SQLite Group By results order behaviour change 3.28.0 onwards

(1.1) Originally by Kingsley with edits by Richard Hipp (drh) on 2020-12-30 19:11:30 from 1.0 [link] [source]

I have noticed an issue with the order of results using "GROUP BY" with later versions of SQLite that am struggling to solve. I first noticed it when Android 11 came out as it uses SQLite 3.28.0. I have now pinned it down to a behaviour change in SQLite itself. The issue is the the order of results is changed depending on the version. I'm not sure if it's a bug or intended behaviour or an error on my part (albeit an error that has been working fine for 8 years with older SQLite versions).

Example table:

id year command code
1 2005 TV A
2 2005 TV-CD B
3 2005 CD B
4 2010 TV B
5 2015 TV C

If I run the following command

SELECT * FROM myTable GROUP BY command ORDER BY _id

With in SQLite 3.22 (Android 10) or 3.23.1 I get:

2 2005 TV-CD B
3 2005 CD B
5 2015 TV C

Which is what I want...

If I run the same command in SQLite 3.28 (Android 11) or higher I get

1 2005 TV A
2 2005 TV-CD B
3 2005 CD B

Here's a quick sample of the table if you want to try it for yourself

CREATE TABLE 'myTable' ('_id' integer PRIMARY KEY  ,'year' NUMERIC ,'command' TEXT, 'code' TEXT);
INSERT INTO myTable VALUES ("1","2005","TV","A");
INSERT INTO myTable VALUES ("2","2005","TV-CD","B");
INSERT INTO myTable VALUES ("3","2005","CD","B");
INSERT INTO myTable VALUES ("4","2010","TV","B");
INSERT INTO myTable VALUES ("5","2015","TV","C");
SELECT * FROM myTable  GROUP BY command ORDER BY _id DESC

https://www.jdoodle.com/execute-sql-online/ was useful for testing as it allows you to change the SQLite version on the fly.

Edited for formatting

(2) By Richard Hipp (drh) on 2020-12-30 19:20:22 in reply to 1.1 [link] [source]

Your query is ambigious. When you have terms in the result set that are not part of the GROUP BY, then the values of those terms can be taken from any row of the ungrouped output that SQLite wants. It is free to choose differently between two consecutive runs of the same query, if it wants.

Apparently what you want is this:

SELECT max(_id), year, command, code
  FROM myTable
 GROUP BY command
 ORDER BY _id DESC;

That query will always give you the same answer, on every run, with every version of SQLite.

(3) By Kingsley on 2020-12-30 19:28:06 in reply to 2 [source]

Thanks for the quick answer, that makes sense and works.