1

I have a table of movies, I want to be able to query the database and get a randomized list of movies, but also I don't want it to return all movies available so I'm using LIMIT and OFFSET. The problem is when I'm doing something like this:

SELECT *  FROM Movie ORDER BY RANDOM() LIMIT 50 OFFSET 0

and then when querying for the next page with LIMIT 50 OFFSET 50 the RANDOM seed changes and so it's possible for rows from the first page to be included in the second page, which is not the desired behavior.

How can I achieve a random order and preserve it through the pages? As far as I know SQLite doesn't support custom seed for it's RANDOM function.

Thank you!

Curtwagner1984
  • 1,908
  • 4
  • 30
  • 48

1 Answers1

2

You cant preserve the random values. You have to add another field name to your table to keep the random order

UPDATE movie
SET randomOrder = Random();

Then you can retrive the pages

SELECT *  
FROM Movie 
ORDER BY randomOrder 
LIMIT 50 OFFSET 0
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • Wouldn't that have a non-negligible impact on performance? If I have 10K movies in the database and need to update each row's `randomOrder` field in order to reshuffle them ? – Curtwagner1984 May 27 '17 at 15:48
  • @Curtwagner1984 Of course. But you have to store the random order *somewhere*, and if the DB needs to use it, it must be in the DB. (You could use a separate, temporary table.) – CL. May 27 '17 at 15:56