Re: Getting a random row

2009/10/14 Scott Marlowe <scott.marlowe@gmail.com>:
>
> If what you're trying to do is emulate a real world app which randomly
> grabs rows, then you want to setup something ahead of time that has a
> pseudo random order and not rely on using anything like order by
> random() limit 1 or anything like that.  Easiest way is to do
> something like:
>
> select id into randomizer from maintable order by random();
>
> then use a cursor to fetch from the table to get "random" rows from
> the real table.
>
>

Why not just do something like:

SELECT thisfield, thatfield
FROM my_table
WHERE thisfield IS NOT NULL
ORDER BY RANDOM()
LIMIT 1;

Thom