Re: [SQL] [GENERAL] How to split a table?

"Gregory S. Williamson" <gsw(at)globexplorer(dot)com> 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 <jgodoy(at)gmail(dot)com>