Brandon, Nicholas wrote:
Hi,
I trying to see if I can use the random(*) function to select a
pseudo-random row and not sure if it will work as I expect.
I want to use it to select a row based on two column values, however I
suspect that each time random(*) is called in a single SQL line, it
retrieves a different number.
An example of how I would like to use it would be:
select *,random(*) from myTable where start_col >= random(*) AND end_col <
random(*);
However, the following SQL generates different random numbers as shown:
sqlite> select random(*), random(*);
733511021|-1742103221
Does that mean that my original SQL would not use a single random number but
multiple?
Another test below using a single column seems to confirm that it uses
different random number for selecting and displaying the results.
sqlite> select random(*) where random(*) between -200000000 AND 200000000;
759396015
sqlite> select random(*) where random(*) between -200000000 AND 200000000;
sqlite> select random(*) where random(*) between -200000000 AND 200000000;
sqlite> select random(*) where random(*) between -200000000 AND 200000000;
sqlite> select random(*) where random(*) between -200000000 AND 200000000;
sqlite> select random(*) where random(*) between -200000000 AND 200000000;
sqlite> select random(*) where random(*) between -200000000 AND 200000000;
sqlite> select random(*) where random(*) between -200000000 AND 200000000;
sqlite> select random(*) where random(*) between -200000000 AND 200000000;
-1605422300
sqlite> select random(*) where random(*) between -200000000 AND 200000000;
Am I trying to do the impossible here?
I've enclosed an "explain" for those that are willing to interpret it for me
sqlite> explain select *,random(*) from myTable where start_col >= random(*)
AND
end_col < random(*);
0|Goto|0|18|
1|Integer|0|0|
2|OpenRead|0|2|
3|SetNumColumns|0|2|
4|Rewind|0|16|
5|Column|0|0|
6|Function|0|0|random(-1)
7|Lt|361|15|collseq(BINARY)
8|Column|0|1|
9|Function|0|0|random(-1)
10|Ge|361|15|collseq(BINARY)
11|Column|0|0|
12|Column|0|1|
13|Function|0|0|random(-1)
14|Callback|3|0|
15|Next|0|5|
16|Close|0|0|
17|Halt|0|0|
18|Transaction|0|0|
19|VerifyCookie|0|1|
20|Goto|0|1|
21|Noop|0|0|
Many Thanks
Nick
********************************************************************
This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender.
You should not copy it or use it for any purpose nor disclose or
distribute its contents to any other person.
********************************************************************
Nick,
Each call to the random() function in your statement generates a new
random number. To reuse the same random number multiple times you need
to save it somewhere. The SQL statement below should do what you want.
It save the random number in a temp table which is joined to your table
(effectively the same random number is appended to each row in your table).
select *, rand.number
from MyTable
join (select random(*) as number) as rand
where start_col >= rand.number
and end_col < rand.number;
HTH
Dennis Cote
