"group by" not required
(1) By anonymous on 2021-03-31 09:58:46 [source]
The following works, although by standard SQL it is not supposed to:
$ sqlite3
SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t (a integer, b integer);
sqlite> insert into t values (1,10), (2,20);
sqlite> select min(a), b from t;
min(a) b
------ --
1 10
sqlite>
The plain "b" in the SELECT obviously cannot appear in the select list like that. PostgreSQL gives the error:
=# select min(a), b from tt;
ERROR: column "tt.b" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select min(a), b from tt;
^
I suppose sqlite picks the row from 't' which corresponds to 'min(a)' and picks up 'b' from that. Actually, this behavior is just what I need in my case -- so the question is: can I rely on this bug/feature/quirk of sqlite?
(2) By Larry Brasfield (larrybr) on 2021-03-31 10:40:28 in reply to 1 [link] [source]
See "3. Generation of the set of result rows.", a few paragraphs down where the behavior is documented under, "Side note: Bare columns in an aggregate queries." This means you can rely upon it. Of course, if the minimum is not unique, there can still be ambiguity in the result.
(3) By Gunter Hick (gunter_hick) on 2021-03-31 12:55:04 in reply to 1 [link] [source]
If you have exactly one aggregate in the SELECT, and that aggregate is min() or max(), SQLite will take all bare column values from any single row that exhibits desired aggregate value within the GROUP. The default GROUP is all records that fulfill the WHERE clause. The default WHERE clause is all records in the table. If there are several rows with the same value matching the desired aggregate value within a group, SQLite is free to choose any one of these rows. AFAIK the row chosen depends on the visitation order, so a change in the query plan (caused by anything from running ANALYZE or VACUUM to changing the SQLite release) may cause a different record to be selected. But it will pick all of the bare columns from the same row.