Sqlite - Order by Rand()

SQLite - ORDER BY RAND()

using random():

SELECT foo FROM bar
  WHERE id >= (abs(random()) % (SELECT max(id) FROM bar))
  LIMIT 1;

---

EDIT (by QOP): Since the docs on SQLite Autoincremented columns states that:

The normal ROWID selection algorithm described above will generate
monotonically increasing unique ROWIDs as long as you never use the
maximum ROWID value and you never delete the entry in the table with
the largest ROWID. If you ever delete rows, then ROWIDs from
previously deleted rows might be reused when creating new rows.

The above is only true if you don't have a INTEGER PRIMARY KEY AUTOINCREMENT column (it will still work fine with INTEGER PRIMARY KEY columns). Anyway, this should be more portable / reliable:

SELECT foo FROM bar
  WHERE _ROWID_ >= (abs(random()) % (SELECT max(_ROWID_) FROM bar))
LIMIT 1;

ROWID, _ROWID_ and OID are all aliases for the SQLite internal row id.

Get random rows from sqlite database, then sort by column

First get the 10 random rows in a subquery and then sort them by difficulty:

SELECT *
FROM (
  SELECT id, difficulty
  FROM tasks
  ORDER BY random()
  LIMIT 10
)
ORDER BY difficulty;

How to mix ASC and RANDOM() on SQLite ORDER BY

select val, id from (select random() as r, * from tabela) order by val, r;

The trick is to make an on-the-fly table (inside the (...)) which has a column with random values. It is possible to order by that.

Note that this query is sorting/ordering complete rows of that on-the-fly table.

Output 1:

paid|1  
paid|3  
paid|2  
standard|8  
standard|5  
standard|7  
standard|6  
standard|4  

Output 2:

paid|3  
paid|1  
paid|2  
standard|5  
standard|8  
standard|7  
standard|4  
standard|6  

Please excuse my impression that you think of ordering columns. It might help you to see the difference, if you use only the subquery in the (...), study the output and then imagine "manually" sorting the rows, while not being allowed to change any of the rows.

This is the query making the on-the-fly table (with additional ordering):

select random() as r, * from tabela order by val, id;  

And its output:

6112298175921944810|1|paid  
-750320757383183987|2|paid  
-4687754812847362857|3|paid  
574487853771424670|4|standard  
6662074554381494613|5|standard  
5947282373212186891|6|standard  
-695595223160523440|7|standard  
-6914056362765123037|8|standard

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.

SQLite: ...EXCEPT ... ORDER BY RANDOM()

In a compound query, the ORDER BY term(s) must match some column in the result set.

You can work around this restriction by moving the compound query into a subquery:

SELECT colour
FROM (SELECT colour FROM ColourView
      EXCEPT
      SELECT colour FROM SkinColourExceptionsView WHERE race = 'Human')
ORDER BY random()
LIMIT 1;

Alternatively, rewrite the query to use a different filter mechanism:

SELECT colour
FROM ColourView
WHERE colour NOT IN (SELECT colour
                     FROM SkinColourExceptionsView
                     WHERE race = 'Human')
ORDER BY random()
LIMIT 1;

sqlite order by random groups

I had to add an ordering to MrSimpleMind's answer in order to achieve the desired order:

WITH albums AS (SELECT DISTINCT album FROM songs ORDER BY RANDOM()) 
SELECT songs.* FROM albums
LEFT JOIN songs
ON songs.album = albums.album
ORDER BY albums.rowid, songs.track

Select random row(s) in SQLite

For a much better performance use:

SELECT * FROM table WHERE id IN (SELECT id FROM table ORDER BY RANDOM() LIMIT x)

SQL engines first load projected fields of rows to memory then sort them, here we just do a random sort on id field of each row which is in memory because it's indexed, then separate X of them, and find the whole row using these X ids.

So this consume less RAM and CPU as table grows!

How to get records from SQLite in random order using the Sequel ORM?

Using a Sequel.lit expression worked:

Term.order(Sequel.lit('RANDOM()')).limit(10)

---

Related Topics