Re: [sqlite] Can you use random(*) to retrieve a pseudo random r owfrom 3.2.3?

[Jakob Hirsch]

Brandon, Nicholas wrote:

> CREATE TABLE MyTable(start_col int,end_col int);
> INSERT INTO "MyTable" VALUES(-2, 2);
...
> sqlite> select * from MyTable join (select random(*) as number) as rand
> where start_col >= rand.number and end_col < rand.number;

Your logic is broken. As every of your row's start_col is less than
end_row, the result set will be empty for every possible value of
rand.number.
Don't even know what you expected to get with this query.

RE: [sqlite] Can you use random(*) to retrieve a pseudo random r owfrom 3.2.3?

[Brandon, Nicholas]

>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

Thanks Dennis for the tip. Did you give it try?

I've tried it and its not selecting any rows after a couple hundred
attempts. I appreciate its "random" but to test I subsequently put in a row
with the min/max random values and that row is still not selected.

CREATE TABLE MyTable(start_col int,end_col int);
INSERT INTO "MyTable" VALUES(-2, 2);
INSERT INTO "MyTable" VALUES(-21, 21);
INSERT INTO "MyTable" VALUES(-21, 21);
INSERT INTO "MyTable" VALUES(-2147483648, 2147483647);

sqlite> select * from MyTable join (select random(*) as number) as rand
where st
art_col >= rand.number and end_col < rand.number;
sqlite>

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