On Thu, 26 Apr 2001, Jie Liang wrote:
>
> How I can return random N rows from my select stmt?
> like:
> e.g. what my selectee is a set of 1000 rows, I want randomly
> pickup 100 of 1000.
Interesting problem.
You might get much better responses than this, but, two ideas
that might be workable:
* use a WHERE clause that checks random() > .88 . This should
give you, on average, about 120 rows out of 1000, and you
can add LIMIT 100 to ensure that you get only 100. But you're
still biased toward the start of the list. (Or, remove the
LIMIT 100, use > .9, but there's no guarantee you'll get 100--
you'll get more or less than that.
* have a plpgsql routine that gets 100 random records,
and copy these into a temporary table (since plpgsql can't
return a recordset.) Query against this table.
Or, when all else fails:
* do it in your front end (Python/Perl/PHP/Pwhatever).
If you get better ideas, and they aren't cc'd to the list, please do so.
HTH,
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington