ORDER BY random() with seed in SQLITE

Short answer:

You can't. SQLite's random() function does not support a seed value.

Not so short answer:

Checking SQLite's func.c shows that random() is defined without any parameters..

VFUNCTION(random,            0, 0, 0, randomFunc       ),

..and this randomFunc() just calls sqlite3_randomness() (again without any explicit seed value) to obtain a random value of sizeof(sqlite_int64) bytes.

Internally, the implementation of sqlite3_randomness() (see random.c) will set up the RC4 pseudo-random number generator the first time it is used with random seed values obtained from the OS:

  /* Initialize the state of the random number generator once,  ** the first time this routine is called.  The seed value does  ** not need to contain a lot of randomness since we are not  ** trying to do secure encryption or anything like that...  **  ** [..]  */  if( !wsdPrng.isInit ){      [..]      sqlite3OsRandomness(sqlite3_vfs_find(0), 256, k);      [..]      wsdPrng.isInit = 1;  }

Actually, SQLite's unit test functions themselves just use memcpy() on the global sqlite3Prng struct to save or restore the state of the PRNG during test runs.

So, unless you're willing to do something weird (like create a temporary table of consecutive numbers (1..max(Animals)), shuffle those around and use them to select 'random-seeded' RowIds from your Animals table) I suppose you're out of luck.

I would not usually copy an existing answer, but I can see that you have left a comment asking the author of this answer to explain how it works already a few weeks ago and no explanation has been given. I will therefore copy the relevant part and try to explain whats going on. If this explanation is good, do go and vote on the original answer.

$seed = md5(mt_rand());$prng = ('0.' . str_replace(array('0', 'a', 'b', 'c', 'd', 'e', 'f'), array('7', '3', '1', '5', '9', '8', '4'), $seed )) * 1;$query = 'SELECT id, name FROM table ORDER BY (substr(id * ' . $prng . ', length(id) + 2)';

The first two rows are just about creating a seed of a sort. The result is a decimal number with lots of decimals like:

0.54534238371923827955579364758491

Then the sql select uses this number to multiply with the numeric row id of every row in the SQLite table. And then the rows are sorted according to the decimal part of the resulting product. Using fewer decimals, the sort order would look something like this:

row id   row id * seed      sort order1        0.545342384        5453423842        1.090684767        0906847673        1.636027151        6360271514        2.181369535        1813695355        2.726711919        7267119196        3.272054302        2720543027        3.817396686        8173966868        4.362739070        362739070

After sorting this would be the result:

row id   row id * seed      sort order2        1.090684767        0906847674        2.181369535        1813695356        3.272054302        2720543028        4.362739070        3627390701        0.545342384        5453423843        1.636027151        6360271515        2.726711919        7267119197        3.817396686        817396686

In this sample I used only eight rows so the result is not very random looking. With more rows the result will appear more random.

This solution will give you the same order repeatedly as long as:

• You use the same seed
• No new rows have appeared in the table and no rows have been deleted from the table

I don't know if you're wanting a PHP and iOS solution, but if you are only interested in iOS and dont care much about using the built-in sqlite random() function, you could declare a custom function to use in your queries, one that does take a seed parameter.

sqlite3_create_function(database, "CUSTOM_RANDOM", 1, SQLITE_UTF8, NULL, &CustomRandomSQLite, NULL, NULL);

.

void CustomRandomSQLite(sqlite3_context* context, int argc, sqlite3_value** argv){    if(argc == 1 && sqlite3_value_type(argv[0]) == SQLITE_INTEGER)    {        const int seed = sqlite3_value_int(argv[0]);        const int result = ...;        sqlite3_result_int(context, result);    }    else    {        sqlite3_result_error(context, "Invalid", 0);    }}

.

Select * from Animals ORDER BY CUSTOM_RANDOM(SEED) LIMIT 100 OFFSET 50