ansaurus

Question

Answer 1

A:

Solved:

SELECT * FROM table ORDER BY RANDOM() LIMIT 1;

Alix Axel 2009-08-10 07:42:03

If you can find the answer within four minutes of asking the question, you probably shoulnd't have asked it at all. Next time: Visit google before stackoverflow.

Emil H 2009-08-10 07:45:46

I disagree. We have two bits of info here now, how to select a single record randomly,how to list all the records randomly. I have never needed to do either, but if I do, now I know how. I also know that MySQL does it different to SQLlite. A super technical question would be more impressive, but less useful.

Chris Huang-Leaver 2009-08-10 08:01:27

My first thought was that there wasn't any function to order results randomly, or if there was such a feature / function it would be considerable more obscure - that's what happens with SQLite triggers for instance.

Alix Axel 2009-08-10 09:30:52

Answer 2

+5 A:

SELECT * FROM table ORDER BY RANDOM() LIMIT 1;

avnic 2009-08-10 07:43:16

Answer 3

+2 A:

using random():

SELECT foo FROM bar
  WHERE id >= (abs(random()) % (SELECT max(id) FROM bar))
  LIMIT 1;

dfa 2009-08-10 07:44:37

+1, This is way faster than the other options provided that id is index.

Emil H 2009-08-10 07:46:21

Yes this solution is faster, but assumes id starts at 1 and has no gaps. Otherwise rows that follow gaps are "randomly" chosen more frequently than other rows.

Bill Karwin 2009-08-10 08:28:49

@Bill, thanks for pointing out the problem on my answer

dfa 2009-08-10 11:55:28

Also the lowest ID will almost never be selected.

Lucky 2009-08-16 02:04:33

ansaurus

tags:

views:

answers:

SQLite - ORDER BY RAND()

related questions