How do I request a single random row from a force.com database in SOQL?

Question

Total row-count is in the range 10k-100k rows. Can I use RAND() on force.com? Unfortunately although all the rows have a unique numeric identifier, there are many gaps, and I'd often want to select a random row from a filtered subset anyway.

I suspect there's no particularly efficient way to do this, but is it possible at all?

Ultimately all I want to do is to extract one row from a table (or a subset based on specific filter criteria) at random.

If force.com doesn't let me select a random row, then can I query the rows to select from, and assign sequential IDs to all the rows, say 1-1,035, and then select a random number in that range locally, say 349, and then get row 349?

Answer 1

You can use SOQL OFFSET to select a random record.

Here's how you do it:

Integer count = [SELECT COUNT() FROM Account];
Integer rand = Math.floor(Math.random() * count).intValue();
Account a = [SELECT Name FROM Account LIMIT 1 OFFSET :rand];
System.debug(a.name);

Answer 2

No, you can't use ORDER BY RAND() or something like that. You can sort by real field (optionally with NULLS LAST etc.). You could use LIMIT, GROUP BY & HAVING though as well as MIN, MAX, COUNT...

Maybe if you'd write more about the purpose for which you need to display a random row... Otherwise what's wrong with ORDER BY LastModifiedDate DESC LIMIT 1? Or selecting 100 rows and showing random row with Math.random() or Crypto.getRandomInteger() modulo 100?