2017-11-06 10:39 GMT+01:00 Keith Medcalf <[email protected]>:
>
> 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-
> >[email protected]] 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
