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

3

7

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?

Undermine answered 10/12, 2010 at 12:28 Comment(0)
L
1

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?

Locris answered 10/12, 2010 at 21:22 Comment(2)
Sorry for delay, been sick. My scenario is simple, I just want to grab a random orow from the entire table, or a subset of it. But the item must be random. I don't see how ordering by lastmodified date helps? If I can't use RAND(), can I run a query to add a new ID column temporarily, get the count, select a random one locally, then pull it out?Undermine
I don't understand the part about "add a new ID column temporarily". If you mean something like ALTER TABLE xyz ADD COLUMN - not possible, columns can be added only from GUI or by modifying the xyz.object file in Eclipse... Then I'm afraid you're really stuck with something like getting fairly large subset of data (10? 10k rows?) and displaying Math.random()'th row from it...Locris
D
14

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);
Durative answered 24/4, 2012 at 16:26 Comment(1)
Will OFFSET work where the "Total row-count is in the range 10k-100k rows"? The maximum offset of 2,000 rows could be problematic with large tables. rand could be capped at 2,000. I don't think the changes to the ordering key without defining the Order By clause will be enough to give a good distribution.Ionopause
M
2

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.

Maible answered 4/6, 2011 at 15:15 Comment(0)
L
1

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?

Locris answered 10/12, 2010 at 21:22 Comment(2)
Sorry for delay, been sick. My scenario is simple, I just want to grab a random orow from the entire table, or a subset of it. But the item must be random. I don't see how ordering by lastmodified date helps? If I can't use RAND(), can I run a query to add a new ID column temporarily, get the count, select a random one locally, then pull it out?Undermine
I don't understand the part about "add a new ID column temporarily". If you mean something like ALTER TABLE xyz ADD COLUMN - not possible, columns can be added only from GUI or by modifying the xyz.object file in Eclipse... Then I'm afraid you're really stuck with something like getting fairly large subset of data (10? 10k rows?) and displaying Math.random()'th row from it...Locris

© 2022 - 2024 — McMap. All rights reserved.