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