2016-11-11 9:49 GMT+01:00 Rowan Worth <row...@dug.com>:
> ORDER BY
> CASE timestamp
> WHEN NULL THEN -9223372036854775808
> ELSE abs(random())*timestamp
> END
> LIMIT 5?
>
> Completely untested, and the weighting function (ELSE clause) is almost
> certainly terrible :P I think the approach is ok, though I remember some
> recent threads suggesting the interaction between ORDER BY and LIMIT is not
> as intuitive as one might expect.
The following looks likes it works reasonable well:
SELECT *
, CASE WHEN julianday(used) IS NULL THEN
randomiser * -1
ELSE
(randomiser / 5) + julianday(used)
END AS ordering
FROM (
SELECT *
, abs(random()) / 100000000000000000 + 1 AS randomiser
FROM proverbs
LIMIT (SELECT COUNT(*) FROM proverbs)
)
ORDER BY ordering
LIMIT 5
Adding the randomiser is better. In this way a selected is never more
as three week used after a not selected one.
I want the spread in the never selected to be a little bigger as the
selected. That is why I use the '/ 5'.
> On 11 November 2016 at 16:26, Cecil Westerhof <cldwester...@gmail.com>
> wrote:
>
>> I have an application that I want to migrate from file-based to
>> sqlite. It displays random quotes. After selecting a quote I update
>> the record with a timestamp.
>>
>> I want to select several quotes, with the following constraints:
>> - As long there are quotes that are not selected, no record that was
>> already selected should be selected.
>> - How longer ago a record was selected, the bigger the chance should
>> be that it is selected.
>>
>> What would the best way to do this? Can this be done in one query? For
>> example: I select 5 records, two records where never selected. So
>> these are the first two selected records. The other three records are
>> selected at random from the already selected ones, where the older
>> ones have a higher chance of getting selected.
>> Is this possible, or should it be done in two queries?
>>
>> I hope I am clear enough.
--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
