2017-11-06 11:11 GMT+01:00 Cecil Westerhof <cldwester...@gmail.com>: > 2017-11-06 10:39 GMT+01:00 Keith Medcalf <kmedc...@dessus.com>: > >> >> The easiest way is likely to make the query so that it cannot be >> flattened by adding an ORDER BY (that does not reference the column >> containing the non-deterministic function by name -- ie, use only OUTPUT >> column position indicators (ordinals), not names or aliases). This will >> require the query planner to use a co-routine for the inner table so that >> only the values will get passed up to the outer query. >> > It looks like that is not necessary.
I played again a little with it. It could be optimised a bit by sorting on the date, so it will be almost sorted correctly in the inner sort. I should just use 3 then. But I just tried what happens if I order by name and that seems to work OK also. I have now: SELECT * , Randomiser , Randomiser FROM ( SELECT * , abs(random()) / CAST(1.4E18 AS INTEGER) AS Randomiser FROM teaInStock ORDER BY "Last Used" ) ORDER BY randomiser + IFNULL(JULIANDAY("Last Used"), 0) ASC LIMIT 5 And this gives: "Goudsbloem" "2017-10-22" "3" "2" "2" "2" "Groene Sencha" "2017-10-29" "B6" "0" "0" "0" "Lemon" "2017-10-24" "B2" "6" "6" "6" "Darjeeling" "2017-10-30" "5" "0" "0" "0" "Ginger Lemon Chai" "2017-10-30" "D4" "1" "1" "1" So you can use the name. Something I prefer vastly above positional. > Depending on the version of SQLite you are using, which you did not >> mention. >> >> SELECT .... >> FROM ( SELECT ... >> FROM ... >> ORDER BY 1) >> ORDER BY ... >> LIMIT ... >> ; >> > > That works likes a charm. I now use: > SELECT "Last Used" > , Randomiser > , Randomiser > , Randomiser + IFNULL(JULIANDAY("Last Used"), 0) > FROM ( > SELECT * > , abs(random()) / CAST(1.4E18 AS INTEGER) AS Randomiser > FROM teaInStock > ORDER BY 1 > ) > ORDER BY randomiser + IFNULL(JULIANDAY("Last Used"), 0) ASC > LIMIT 5 > > And this gives for example: > > > "2017-10-17" "2" "2" "2458045.5" > "2017-10-20" "0" "0" "2458046.5" > "2017-10-19" "3" "3" "2458048.5" > "2017-10-18" "6" "6" "2458050.5" > "2017-10-19" "5" "5" "2458050.5" > > So problem solved. The double order is inefficient, but is only used > during debugging, which now works. :-D > > Thanks. > > > By the I am testing it in “DB Browser for SQLite” which uses 3.15.2. > > I am not sure in which programming language the real version will be > implemented, but it will probably use a version near that one. For example > my Java uses 3.20.0. > -- Cecil Westerhof _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users