What would the SQL statement be to randomly select 30% of the records
in table2 and calculate column X equal to string 'test' for just those
random records? I just have no idea how to get the random set of
records. How?

Thanks for any help!

On 08-06-2011 16:30, noydb wrote:
A select statement would look something like:

select t1.i, t1.t
from testText t1 , (select count(*) c
from testText) t2
where rand()*c < t2.c*0.30;

But it dows not select exactly 30%.....

--
Luuk

noydb wrote:
This gives you the randomness:
select * from table order by rand()


--

//Aho

J.O. Aho wrote:

Not seen that one..

then a limit statement formed as a subquery from a count of the rows
sorts the rest..


MM that's neat

select id from day order by rand() limit 10;
+------+
10 rows in set (0.04 sec)


the problem is that whilst establishing the number of 30% rows is easy
enough..

mysql> (select floor(count(id)*0.3) as size from day);
+------+

...mysql wont take the output from a subquery as an argument t limit..

mysql> select id from day order by rand()
limit (select floor(count(id)*0.3) as size from day);
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near '(select floor(count(id)*0.3) as size from day)' at
line 1
mysql>

The Natural Philosopher wrote:

Yeah, you are so right about that, I even tried with a CREATE FUNCTION to make
it to work, but it didn't like my limit with a variable.

--

//Aho

J.O. Aho wrote:
language, then.

On 2011-06-09 21:55, The Natural Philosopher wrote:

It's possible (but probably inefficient) to mimic the row_number() olap
function from sql99 using variables:

select x from (
select x, @a := @a+1 from (
select @a := 0, @b := (select count(1) from T), x
from T
order by rand()
) as V
where @a < 0.3*@b
) as U;

/Lennart