SQLite Forum

Can this simple query be optimized?
Login
>  I vaguely recall reading something about the non-standard behavior of sqlite's min/max function in the official documentation, but I can't find it now

Well, it's specifically NOT documented because it isn't a feature, it's merely a happenstance that goes something like this:

[Edit]: This statement is wrong - there *is* [clear documentation here](<https://sqlite.org/lang_select.html#bareagg>) as noted by Keith in reply.
The rest of this message may still be useful to visitors as an explanation, kept in with some edits:

SQLite very kindly does not require non-aggregate values to be in aggregate functions in an aggregate query, yet will typically pick the values adjacent to those picked in the aggregate function to show in the non-aggregate results of the same row. 

It is *not* in SQL standard/rules and *no* other DB engine does this[1].

I hope that statement is clear, but just in case, here is an example - suppose this schema:

```
CREATE TABLE t(x,y);
INSERT INTO t(x,y) VALUES
 (1, 15)
,(2, 93)
,(3, 12)
,(4, 81)
;

Now an aggregate query like this:

SELECT x, MAX(y) FROM t GROUP BY 'A';

It will produce in SQLite (and ONLY in SQLite):
  x  |  y
 --- | ---
  2  |  93
```

In MSSQL it won't run, complaining of not allowing non-aggregates in Aggregate queries (as it should)
and in MySQL (sans "Strict" mode anyway) it would accept it, but could easily return:

```
  x  |  y
 --- | ---
  1  |  93
or maybe
  x  |  y
 --- | ---
  4  |  93
```
i.e. the x would be completely unrelated to the aggregated max(y).


> ... is taking 3x as long as the original "non-standard" query (the non-standard query itself being 5x slower than my manual aggregation)

Well yes, you are asking a general-case DB engine to do computations in a general construct under strict adherence to relational algebraic rules,
to compete in a rigged race where the other contestant (your code) made very well by a good programmer with immaculate insight into the content of the data and no care about SQL standards adherence... 

It is little wonder the other contestant won.
If you *can* do it in code, *and* you really care about the speed, you should.


> ...so if that behavior isn't likely to change I might continue to use the "non-standard" behavior in the future.

Yes - it is documented and therefor will be supported so until 2050!


[1] I can't of course be 100% sure of this, I don't use every DB engine, nor know all the most recent updates to the ones I do use, so it may be someone somewhere also has this quirk that I'm unaware of - however, that is beside the point.