Re: [sqlite] Can you use random(*) to retrieve a pseudo random row from 3.2.3?
On Mon, 2005-08-22 at 16:54 -0600, Dennis Cote wrote: > select * > from MyTable > join (select random(*) as number) as rand > where start_col >= rand.number > and end_col < rand.number; > Very nice. Dennis Cote wins todays prize for cleverest use (abuse?) of a join! -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Can you use random(*) to retrieve a pseudo random row from 3.2.3?
Dennis Cote wrote: 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; Nick, I just thought I should clarify my SQL. The select clause should be select * from MyTbale join... since the random number is already included in each row of the joined table. Dennis Cote
Re: [sqlite] Can you use random(*) to retrieve a pseudo random row from 3.2.3?
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 -2 AND 2; 759396015 sqlite> select random(*) where random(*) between -2 AND 2; sqlite> select random(*) where random(*) between -2 AND 2; sqlite> select random(*) where random(*) between -2 AND 2; sqlite> select random(*) where random(*) between -2 AND 2; sqlite> select random(*) where random(*) between -2 AND 2; sqlite> select random(*) where random(*) between -2 AND 2; sqlite> select random(*) where random(*) between -2 AND 2; sqlite> select random(*) where random(*) between -2 AND 2; -1605422300 sqlite> select random(*) where random(*) between -2 AND 2; 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
[sqlite] Can you use random(*) to retrieve a pseudo random row from 3.2.3?
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 -2 AND 2; 759396015 sqlite> select random(*) where random(*) between -2 AND 2; sqlite> select random(*) where random(*) between -2 AND 2; sqlite> select random(*) where random(*) between -2 AND 2; sqlite> select random(*) where random(*) between -2 AND 2; sqlite> select random(*) where random(*) between -2 AND 2; sqlite> select random(*) where random(*) between -2 AND 2; sqlite> select random(*) where random(*) between -2 AND 2; sqlite> select random(*) where random(*) between -2 AND 2; -1605422300 sqlite> select random(*) where random(*) between -2 AND 2; 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.
