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