In MySQL I can use the RAND() function, is there any alternative in SQLite 3?
5 Answers
-
6And for the record the limit doesn't have to be 1 if you want to order the entire table randomly and access all of the rows in that random order. – lemontwist Jul 31 '12 at 20:06
-
2This will also work if you have a complex WHERE clause and want a random row from that filtered list. The accepted answer does not support that easily. – Cory Trese Nov 22 '13 at 04:01
-
And One More this there is no duplicate row returned in Result, This is what I need (y) – Hitesh Chavda Jan 11 '15 at 09:06
-
why I use it has this message of wrong information:1st ORDER BY term does not match any column in the result set – sikisis Mar 23 '15 at 12:42
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.
-
2+1, This is way faster than the other options provided that id is index. – Emil H Aug 10 '09 at 07:46
-
17Yes this solution is faster, but assumes id starts at 1 and has no gaps. Otherwise rows that follow gaps are "randomly" chosen more frequently than other rows. – Bill Karwin Aug 10 '09 at 08:28
-
1
-
2
-
Very good soluce to ge ONLY 1 row randomly, but doesn't work well with a limit bigger than 1 :/ Indeed, I made a test with only 4 inputs im my DDB and a limit of 5 => sometime I have 3 results, sometime 4 results... I think there will be a better random with bigger DDB, but not for the little one. – LE GALL Benoît Jan 11 '14 at 14:35
-
This seems to select one row from the table at random, without trying to randomize the order; whereas the question title is about ORDER BY. In other words this answer doesn't answer the question. Yet it's highly voted and accepted. What gives? – LarsH Mar 06 '20 at 21:39
-
Agree. This was at least an order of magnitude faster than 4-5 other queries I found on the web. – Tom Aug 27 '20 at 18:48
Solved:
SELECT * FROM table ORDER BY RANDOM() LIMIT 1;

- 151,645
- 95
- 393
- 500
-
8I disagree. We have two bits of info here now, how to select a single record randomly,how to list all the records randomly. I have never needed to do either, but if I do, now I know how. I also know that MySQL does it different to SQLlite. A super technical question would be more impressive, but less useful. – Chris Huang-Leaver Aug 10 '09 at 08:01
-
My first thought was that there wasn't any function to order results randomly, or if there was such a feature / function it would be considerable more obscure - that's what happens with SQLite triggers for instance. – Alix Axel Aug 10 '09 at 09:30
For a much better performance use this in SQLite:
SELECT * FROM table WHERE id IN (SELECT id FROM table ORDER BY RANDOM() LIMIT x)
This is also applicable to MySQL. This runs faster because SQL engines first load projected fields of rows to memory then sort them, here we just load and random sort the id field of rows, then we get X of them, and find the whole rows of these X ids which is by default indexed.

- 21,572
- 15
- 83
- 95
-
2+1. In my environment this is ~30 times faster compared to `SELECT * FROM table ORDER BY RANDOM() LIMIT 1`, and still gives true random w/o any requirements to table schema. – Alex Che Apr 02 '19 at 11:16
-
The goal is to get random results (Infinite Scroll) while being able to SQL paginate the results (LIMIT a,b
), which needs a predictible outcome (pseudorandom aka PRNG).
SIN(id + seed)
seems a great alternative to RANDOM(seed)
.
Please consider this demo entirely written in JS that simulates an ORDER BY
clause using a SIN(id + seed)
scoring :
// Inspired by:
// https://www.sqlite.org/forum/forumpost/e2216583a4
// https://stackoverflow.com/a/24511461/7776828
// Simulate N autoincrement stable ids
// (Avoid rowid which is unstable)
const max = 20;
const a = Array();
for (let id = 0; id < max; ++id) {
a.push({id});
}
console.log(a);
// Order the results by random
const orderByRandom = ({a, seed}) => {
// For each result,
// Use sin(id + seed) to get a stable random score
const randomScored = a.map(x => {
return { ...x, score: Math.sin(x.id + seed) }
});
// Sort by the random score
randomScored.sort((a,b) => a.score - b.score);
return randomScored;
}
// Used for generating the seed
const random = () => 1 + Math.floor(Math.random() * Number.MAX_SAFE_INTEGER - 1);
let seed;
seed = random(); // seed #1
console.log(orderByRandom({a, seed}));
console.log(orderByRandom({a, seed})); // Stable, can paginate
seed = random(); // seed #2
console.log(orderByRandom({a, seed})); // New order because new seed

- 1,017
- 10
- 11
-
Thanks! I updated to the latest version of sqlite3 in Node.js (it includes the math functions) and this approach worked. – Matt Montag Jan 17 '23 at 21:40