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. ********************************************************************
