2016-11-11 9:49 GMT+01:00 Rowan Worth <[email protected]>: > 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.
I am going to play with it. Thanks. > On 11 November 2016 at 16:26, Cecil Westerhof <[email protected]> > 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 [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
