Re: [sqlite] selecting a random record from a table
On 1/25/07, P Kishor <[EMAIL PROTECTED]> wrote: 1. given a non-sequential id, select all the ids 2. grab a random id 3. select the row with that id. is there a better way of accomplishing this, one that requires a single round-trip to the db? There's always the random shuffle method. add a column to the table called 'sortorder' assign a random number to that column each time you want the list reordered. Then use Select * from mytable order by sortorder limit 1 That method is useful where you need a specific list in random order, like for card shuffles. If you use a hash you usually get repeats of some elements and some left out.
Re: [sqlite] selecting a random record from a table
(Pardon me if this email is a duplicate, but I think the last copy was interpreted as spam due to the repetition of rows in the SQL.) This is not strictly random, but will select a pseudo-random row in near constant time regardless of the number of rows in the table: -- let's assume you know you only have 30 or fewer rows in table1 select * from table1 where rowid = random() & 31 union all select * from table1 where rowid = random() & 15 union all select * from table1 where rowid = random() & 7 union all select * from table1 where rowid = random() & 3 union all select * from table1 where rowid = random() & 1 limit 1; Yes, I know rowid's are not necessarily contiguous, but you can extend the pattern to a couple of billion rows if you like. Bored stiff? Loosen up... Download and play hundreds of games for free on Yahoo! Games. http://games.yahoo.com/games/front - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] selecting a random record from a table
This is not strictly random, but will select a row in near constant time regardless of the number of rows in the select: -- let's assume you know you only have 65535 rows... select * from table1 where rowid = random() & 65535 union all select * from table1 where rowid = random() & 32767 union all select * from table1 where rowid = random() & 16383 union all select * from table1 where rowid = random() & 8191 union all select * from table1 where rowid = random() & 4095 union all select * from table1 where rowid = random() & 2047 union all select * from table1 where rowid = random() & 1023 union all select * from table1 where rowid = random() & 511 union all select * from table1 where rowid = random() & 255 union all select * from table1 where rowid = random() & 127 union all select * from table1 where rowid = random() & 63 union all select * from table1 where rowid = random() & 31 union all select * from table1 where rowid = random() & 15 union all select * from table1 where rowid = random() & 7 union all select * from table1 where rowid = random() & 3 union all select * from table1 where rowid = random() & 1 limit 1; Yes, I know rowid's are not necessarily contiguous, but you can extend the pattern to a couple of billion rows if you like. Never Miss an Email Stay connected with Yahoo! Mail on your mobile. Get started! http://mobile.yahoo.com/services?promote=mail - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] selecting a random record from a table
> select * from table1 order by random() limit 1 If you're performing a select on a single table (not a view, join or a subquery) and you are selecting many columns (say > 6) and the table has a very large number of rows, then this query will run faster on recent versions of sqlite3: select * from table1 where rowid = (select rowid from table1 order by random() limit 1); To see why: create table j1(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p); explain select * from j1 order by random() limit 1; explain select * from j1 where rowid = (select rowid from j1 order by random() limit 1); Food fight? Enjoy some healthy debate in the Yahoo! Answers Food & Drink Q http://answers.yahoo.com/dir/?link=list=396545367 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] selecting a random record from a table
On 1/25/07, Artem Yankovskiy <[EMAIL PROTECTED]> wrote: select * from table1 order by random(id) limit 1 Yes, very nice, thank you. I am not familiar with the "ORDER BY random(col)" idiom. How does this work? (It does work alright). --- P Kishor <[EMAIL PROTECTED]> wrote: > 1. given a non-sequential id, select all the ids > 2. grab a random id > 3. select the row with that id. > > is there a better way of accomplishing this, one > that requires a > single round-trip to the db? > .. Best regards, Artem Yankovskiy -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] selecting a random record from a table
select * from table1 order by random(id) limit 1 ? --- P Kishor <[EMAIL PROTECTED]> wrote: > 1. given a non-sequential id, select all the ids > 2. grab a random id > 3. select the row with that id. > > is there a better way of accomplishing this, one > that requires a > single round-trip to the db? > > -- > Puneet Kishor http://punkish.eidesis.org/ > Nelson Inst. for Env. Studies, UW-Madison > http://www.nelson.wisc.edu/ > Open Source Geospatial Foundation > http://www.osgeo.org/education/ > - > collaborate, communicate, compete > = > > - > To unsubscribe, send email to > [EMAIL PROTECTED] > - > > Best regards, Artem Yankovskiy Вы уже с Yahoo!? Испытайте обновленную и улучшенную. Yahoo! Почту! http://ru.mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] selecting a random record from a table
1. given a non-sequential id, select all the ids 2. grab a random id 3. select the row with that id. is there a better way of accomplishing this, one that requires a single round-trip to the db? -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
