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. > > 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. > >-----Original Message----- > >From: sqlite-users [mailto:sqlite-users- > >boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof > >Sent: Monday, 6 November, 2017 01:16 > >To: SQLite mailing list > >Subject: [sqlite] How not to let random be calculated again and again > >and > > > >I have a query that I use to randomly select a set of records, but an > >older > >one should have a higher change and a never used record is selected > >before > >a used record. For this I use a query that looks a bit like this: > > SELECT "Last Used" > > , Randomiser > > , Randomiser > > , Randomiser + IFNULL(JULIANDAY("Last Used"), 0) > > FROM ( > > SELECT * > > , abs(random()) / CAST(1.4E18 AS INTEGER) AS > >Randomiser > > FROM foo > > ) > > ORDER BY randomiser + IFNULL(JULIANDAY("Last Used"), 0) ASC > > LIMIT 5 > > > >But Randomiser is calculated every-time it is used. For example this > >just > >gave: > > "2017-10-20" "1" "1" "2458046.5" > > "2017-10-18" "0" "3" "2458047.5" > > "2017-10-19" "5" "5" "2458047.5" > > "2017-10-17" "2" "5" "2458048.5" > > "2017-10-20" "3" "1" "2458048.5" > > > >Is there a way to generate Randomiser in such a way it is only > >calculated > >once pro record? > -- Cecil Westerhof _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users