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.
-Rowan
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
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
