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.