Random Sample

On Fri, 2007-05-18 at 15:36 -0500, tom@tacocat.net wrote:

How do I pull a random sample of either 100 records or 5% of the
population of a table?

If you can be a little flexible about the number of samples, you can try

select * from table where random()<=0.05;

Of course, there's nothing that guarantees that you'll get 5% and this
only works reasonably for large N. On the other hand, if N were small,
you probably wouldn't be asking for a random sample.

You could also try

select * from table order by random() limit 100;

That'll be expensive, but get you exactly 100 (if your table has >= 100
rows, of course).

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

How do I pull a random sample of either 100 records or 5% of the
population of a table?

SELECT table.* FROM table
ORDER BY random()
LIMIT n;

Yours,
Laurenz Albe