> Tthe problem with this is that this is not very random.
> If the uids 3 to 3 have been missing, but
> the uids are more or less contiguous apart from that,
> the uid 4 would be 1 times more likely to be selected
> than average.
There are some gaps but distribution of them is qui
"gnari" <[EMAIL PROTECTED]> writes:
> Tthe problem with this is that this is not very random.
> If the uids 3 to 3 have been missing, but
> the uids are more or less contiguous apart from that,
> the uid 4 would be 1 times more likely to be selected
> than average.
There is some di
From: "Kari Lavikka" <[EMAIL PROTECTED]>
>
> 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((SELE
Re: [GENERAL] Selecting a
random row
Kari Lavikka <[EMAIL PROTECTED]> 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()
Replying to myself..
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
IIRC, this was discussed a few times on this list, searching the
archives might get you some results. AFAIR, the only way to do it
efficiently is to have a column specially assigned for this purpose, and
populate it with random numbers in a big range. The column should be
indexed to assure fast acc
Works but is too slooow. Shuffling whole table and selecting the first
row is not the way to go in this case.
Limit (cost=5340.74..5340.74 rows=1 width=4)
-> Sort (cost=5340.74..5440.70 rows=39986 width=4)
Sort Key: random()
-> Seq Scan on users (cost=0.00..2284.37 rows
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Thursday 04 November 2004 12:36, Kari Lavikka wrote:
> Is there any explanation for this strange behavior or are there better
> ways to select a random row?
How about
SELECT ...whatever... ORDER BY random() LIMIT 1;
Mit freundlichem Gruß
Hi!
I have to select a random row from a table where primary key isn't
continuous (some rows have been deleted). Postgres just seems to do
something strange with my method.
--
-- Use the order by desc limit 1 -trick to get maximum value
--
CREATE OR REPLACE FUNCTION max_uid() RETURNS int4 AS
'
10 matches
Mail list logo
