with query1 as (
select user_id, cast (rand() as float) as random1
from master_table where dt='2015-12-17'
select * from query1
0 A60028682230369691 0.00047147460281848907
1 A60028682230362251 0.58958953619003296
2 A60028682230777191 0.44319009780883789
But when I add a where clause to compare value with the random1 column it doesn't work. For example:
select * from query1 where random1 < 0.2 Result: returns all rows
select * from query1 where random1 < 0 Result: returns 0 row (which is correct)
Tried all kinds of variations, including
- cast as float everywhere
- multiplying the rand() by 1000 and cast as int
- do the comparison and store true/false in the column in query1, and then select with where clause random1=true
none of the above can give me a portion of the data set. Am I missing something for this simple query? It's not about the randomness of the rand() but the use of the rand() result in a where clause.