Re: Selecting a random row

From: "Kari Lavikka" <tuner(at)bdb(dot)fi>
>
> Actually I found an answer. If a I wrap the split point selection to
> subquery then the range of results is from 0 to maximum value (~120k in
> this case)
>
> galleria=> SELECT u.uid FROM users u WHERE u.status = 'a' AND uid >=
> (select cast(cast((SELECT uid FROM users WHERE status = 'a' ORDER BY uid
> DESC LIMIT 1) - 1 AS FLOAT) * random() AS INTEGER)) ORDER BY uid ASC LIMIT
1;
> uid
> -------
> 91937
> (1 row)

Tthe problem with this is that this is not very random.
If the uids 30000 to 39999 have been missing, but
the uids are more or less contiguous apart from that,
the uid 40000 would be 10000 times more likely to be selected
than average.

Maybe using an OFFSET of (count(*) * random()) and a LIMIT 1
could be practical.

gnari