Selecting a random result from Simple Database (SDB) on AWS

Question

Anyone know how to do this - just what would be the equivalent to this: "select * from YOUR_TABLE order by rand() limit 1" in mysql??

Maybe not possible in SDB?

Answer 1

No, there's no type of random function in SimpleDB. You'd have to implement the random part yourself.

You could do something like:

count = sdb.select("select count(*) from YOUR_TABLE")
random = (rand() * count) + 1
nextToken = sdb.select("select count(*) from YOUR_TABLE limit " + random)
item = sdb.select("select * from YOUR_TABLE limit 1" , nextToken)

But this takes a minimum of three queries. The first one would have to be repeated until you got the full count (no NextToken). The second one would need to be repeated until you reach a count of random (2500 per request max) unless you saved some NextTokens from the first set of queries.

All in all not very convenient.

Answer 2

I actually talked to an Amazon rep about this. What you're supposed to do is store a random value along with your data in SDB. When you want a row back, you generate another random value and pick the first result less than that. So you need to store a little more data, but it only takes one query.