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

Well, you can’t really use  ORDER BY RAND() or something like that. But, you can sort by real field (optionally with NULLS LAST, etc.). But, you can use GROUP BY, LIMIT and HAVING or MIN, COUNT, MAX, etc. 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, what if you select 100 rows and show random row with Math.random() or Crypto.getRandomInteger() modulo 100? I guess that would work well as well.