Sample Header Ad - 728x90

Select random records until a cumulative total is exceeded

0 votes
2 answers
83 views
I have a table of videos with an id, skill_level and duration column and I am attempting to write a query that will return a playlist of distinct videos that has a total duration exceeding X.
select _id, duration, running_total
from (select *, sum(duration) over (order by RANDOM()) as running_total
      from videos
      where skill_level in ('beginner', 'superbeginner') AND watched IS NULL
     )
where running_total <= 7200;
This works and returns the following:
5e4d11fbaac87f3820954c45|305|305
64e51ad279223a9ad03525c2|930|1235
5e4d1204aac87f3820954c64|627|1862
5e4d1212aac87f3820954c95|367|2229
641e05c2d7c1512d278dc51a|361|2590
5e4d1200aac87f3820954c57|444|3034
649aa7089776ba12d8de28d5|329|3363
5e4d123eaac87f3820954d2c|573|3936
5e4d1216aac87f3820954ca1|399|4335
5e4d120daac87f3820954c84|315|4650
5e4d11dfaac87f3820954be1|510|5160
64f7036d3d578af923ba2afc|326|5486
5e4d11fcaac87f3820954c47|437|5923
5fa51afc27a912152cc51c33|1062|6985
But ideally I want to return enough records to exceed X but only by one. 6375274c1cff4a7e31b04a0d below puts us over 7200.
5e4d11fbaac87f3820954c45|305|305
64e51ad279223a9ad03525c2|930|1235
5e4d1204aac87f3820954c64|627|1862
5e4d1212aac87f3820954c95|367|2229
641e05c2d7c1512d278dc51a|361|2590
5e4d1200aac87f3820954c57|444|3034
649aa7089776ba12d8de28d5|329|3363
5e4d123eaac87f3820954d2c|573|3936
5e4d1216aac87f3820954ca1|399|4335
5e4d120daac87f3820954c84|315|4650
5e4d11dfaac87f3820954be1|510|5160
64f7036d3d578af923ba2afc|326|5486
5e4d11fcaac87f3820954c47|437|5923
5fa51afc27a912152cc51c33|1062|6985
6375274c1cff4a7e31b04a0d|1138|8123
Is it possible to write a query to achieve this in sqlite?
Asked by Ashley (103 rep)
Feb 21, 2024, 03:52 AM
Last activity: Feb 27, 2024, 04:04 PM