Re: Selecting a random row

Kari,

Why not select count(*) from the table and multiply it by a true 0.0 - 1.0
pseudo random number generator? Then adjust the outcome for the range of
uids. If the uids (or some other column) are contiguous starting at 0,
this would be a snap.

Rick

Tom Lane
<tgl(at)sss(dot)pgh(dot)pa(dot)us> To: Kari Lavikka <tuner(at)bdb(dot)fi>
Sent by: cc: pgsql-general(at)postgresql(dot)org
pgsql-general-owner(at)pos Subject: Re: [GENERAL] Selecting a random row
tgresql.org


11/04/2004 10:25 AM

Kari Lavikka <tuner(at)bdb(dot)fi> writes:
> --
> -- Choose a random point between 0 and max_uid and select the first
> -- value from the bigger part
> --
> CREATE OR REPLACE FUNCTION random_uid() RETURNS int4 AS
> 'SELECT uid FROM users u WHERE u.status = ''a'' AND uid >=
> cast(cast(max_uid() - 1 AS FLOAT) * random() AS INTEGER) ORDER BY uid
> ASC LIMIT 1'
> LANGUAGE 'sql';

This isn't going to do what you think because the random() function is
re-evaluated at every row of the table. (For that matter, so is
max_uid(), which means performance would suck even if it worked ...)

I'd suggest rewriting in plpgsql so you can assign the (max_uid-1)*random()
expression to a variable and then just use the variable in the SELECT.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings