Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [206.210.65.6]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA28363 for ; Wed, 30 Jun 1999 10:29:53 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id KAA22062; Wed, 30 Jun 1999 10:28:51 -0400 (EDT) To: dgreer@websightsolutions.com cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] More fun with random selects In-reply-to: Your message of Wed, 30 Jun 1999 07:27:02 -0500 <9906300730281H.06064@george> Date: Wed, 30 Jun 1999 10:28:51 -0400 Message-ID: <22060.930752931@sss.pgh.pa.us> From: Tom Lane Darren Greer writes: > For example, lets say I want a random 125 rows from an existing table. Is > there an easy way I can do that where I actually am getting a decent sample > from the table? I don't think there's any way to do that with a simple SQL command. It's possible to draw exactly N items from a set fully randomly if you are willing to write a little code. The algorithm looks like this: itemsRemaining = size of set (# rows in table); itemsStillNeeded = N (# rows wanted, 125 in your example); foreach (item in set) { generate random value X that is 'true' with probability itemsStillNeeded / itemsRemaining; if (X) { emit current item as a selected item; decrement itemsStillNeeded; if (itemsStillNeeded == 0) done; } decrement itemsRemaining; } The random choice is typically done with a random number generator that generates output G between say 0 and M; you make X 'true' if G <= M * itemsStillNeeded / itemsRemaining. regards, tom lane