Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id BB85F9FA3B4 for ; Tue, 17 Oct 2006 07:52:32 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 68854-07 for ; Tue, 17 Oct 2006 10:52:26 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.186]) by postgresql.org (Postfix) with ESMTP id 0ADE39FA3D0 for ; Tue, 17 Oct 2006 07:52:25 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id i2so268999nfe for ; Tue, 17 Oct 2006 03:52:24 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:from:to:cc:subject:in-reply-to:references:date:message-id:user-agent:mime-version:content-type:x-virus-scanned; b=QKGYI76PlhIv++8C8w7h+SY/nwEArq4Vu2qlbSTu6tqO2pJWDbzKkl+C8BslutZEl+8f6eq9pTU4RcCtbLsqNfrXrquSzqdM9/pc88Auou4te0OZNdJzoyrqRDmhD2lyLuQzQ6kRAhm+MGHAfhh40uBcfk/WFN0zN/vSyW30SRM= Received: by 10.49.93.13 with SMTP id v13mr1325419nfl; Tue, 17 Oct 2006 03:52:24 -0700 (PDT) Received: from strongwill.g2ctech ( [201.47.19.248]) by mx.google.com with ESMTP id y24sm872295nfb.2006.10.17.03.52.21; Tue, 17 Oct 2006 03:52:23 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by strongwill.g2ctech (Postfix) with ESMTP id 1331CD7799; Tue, 17 Oct 2006 08:52:17 -0200 (BRST) Received: from strongwill.g2ctech ([127.0.0.1]) by localhost (strongwill.g2ctech [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 01957-04; Tue, 17 Oct 2006 08:52:15 -0200 (BRST) Received: from jupiter.g2ctech (unknown [10.0.0.240]) by strongwill.g2ctech (Postfix) with ESMTP id D0C2377DA5; Tue, 17 Oct 2006 08:52:15 -0200 (BRST) Received: by jupiter.g2ctech (Postfix, from userid 1000) id 3E9A517C6F2; Tue, 17 Oct 2006 08:51:49 -0200 (BRST) From: Jorge Godoy To: "Gregory S. Williamson" Cc: "A. Kretschmer" , , Subject: Re: [SQL] [GENERAL] How to split a table? In-Reply-To: <71E37EF6B7DCC1499CEA0316A256832802B3E737@loki.wc.globexplorer.net> (Gregory S. Williamson's message of "Tue\, 17 Oct 2006 02\:19\:47 -0700") References: <20061017083418.GA3917@KanotixBox> <71E37EF6B7DCC1499CEA0316A256832802B3E735@loki.wc.globexplorer.net> <20061017091237.GA19268@a-kretschmer.de> <71E37EF6B7DCC1499CEA0316A256832802B3E737@loki.wc.globexplorer.net> Date: Tue, 17 Oct 2006 08:51:49 -0200 Message-ID: <87pscrqko3.fsf@gmail.com> User-Agent: Gnus/5.110006 (No Gnus v0.6) Emacs/21.3 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: amavisd-new at g2ctech.com X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.199 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, SPF_PASS X-Spam-Level: X-Archive-Number: 200610/141 X-Sequence-Number: 17908 "Gregory S. Williamson" writes: > A crude approach would be to add a column to the original table; then update > that based on the rand() call: > > update foo set i_am_a_60 = 1 where (rand() <= 0.60); > create table foo_60 as select * from foo where i_am_a_60 = 1; > create table foo_40 as select * from foo where i_am_a_60 <> 1; > > The CASE condition might be usable as well but I haven't puzzled it out ... If he's asking that 60% of the contents of the original table be randomly selected this won't work. He'll have to count how many rows the original table has, then loop randomly selecting rows until he has reached 60% of that total. Otherwise he might end up with something completely different from what he wants. On the other hand if he wants rows whose randomness factor at the time they were looked at was bigger than 0.6 then he can use that rand() trick. -- Jorge Godoy