Can you use random(*) to retrieve a pseudo random row from 3.2.3?

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Can you use random(*) to retrieve a pseudo random row from 3.2.3?

Brandon, Nicholas (UK)

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.
********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Can you use random(*) to retrieve a pseudo random row from 3.2.3?

Dennis Cote
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

Reply | Threaded
Open this post in threaded view
|

Re: Can you use random(*) to retrieve a pseudo random row from 3.2.3?

Dennis Cote
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
Reply | Threaded
Open this post in threaded view
|

Re: Can you use random(*) to retrieve a pseudo random row from 3.2.3?

D. Richard Hipp
In reply to this post by Dennis Cote
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 <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Please unsubscribe me

Bill Henderson
In reply to this post by Dennis Cote
I have tried uncuccessfully to unsubscribe several times
 
PLEASE REMOVE ME FROM THIS LIST

               
---------------------------------
 Appel audio GRATUIT partout dans le monde avec le nouveau Yahoo! Messenger
 T?l?chargez le ici !