Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f3QJa3317985 for ; Thu, 26 Apr 2001 15:36:03 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [207.172.28.154] (HELO agliodbs.com) by davinci.ethosmedia.com (CommuniGate Pro SMTP 3.4.1) with ESMTP id 44629; Thu, 26 Apr 2001 12:36:01 -0700 Message-ID: <3AE87847.F943E9E6@agliodbs.com> Date: Thu, 26 Apr 2001 12:34:31 -0700 From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions X-Mailer: Mozilla 4.73 [en] (Win98; I) X-Accept-Language: en MIME-Version: 1.0 To: Jie Liang , sqllist Subject: Re: random rows References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200104/412 X-Sequence-Number: 2774 Jie, > 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. You'd have to do it inside a function or external program, and copy the rows to a temporary table (which is what you'd return to the user). Thus, language-agnostic rules: CREATE FUNCTION return_random(X) LOOP X Times totalrecs = COUNT(*) FROM maintable WHERE NOT EXISTS temptable offset_count = RANDOM*totalrecs INSERT INTO temptable ( a, b, c, d ) SELECT a, b, c, d FROM maintable LIMIT 1 OFFSET offset_count; END LOOP END; than: SELECT temptable -Josh -- ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco