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 ...
;
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
