sqlmysqldatabasesqliterandom-access

SQLite - ORDER BY RAND()

In MySQL I can use the RAND() function, is there any alternative in SQLite 3?

Solution

  • 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.