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

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 p开发者_开发问答articularly 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?

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);

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?

You could try something like this.

1. Add a Sequence Column starting from 0.

2. Use Math.random() - which will return a decimal ranging between 0 to 1. multiply that by 100 r 1000 to get integer.

3. use SOQL to fetch that row SELECT Bar__c, Bar_Seq_Col_c from Foo_c where Bar_Seq_Col__c = :Math.random() * 10

this is just a sample idea you can think of these lines to see is it a feasiable idea.