Re: Selecting a random row
От | gnari |
---|---|
Тема | Re: Selecting a random row |
Дата | |
Msg-id | 002301c4c292$3beb32e0$0100000a@wp2000 обсуждение исходный текст |
Ответ на | Selecting a random row (Kari Lavikka <tuner@bdb.fi>) |
Ответы |
Re: Selecting a random row
Re: Selecting a random row |
Список | pgsql-general |
From: "Kari Lavikka" <tuner@bdb.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
В списке pgsql-general по дате отправления: