> 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.