[sqlite] How not to let random be calculated again and again and

[Cecil Westerhof]

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