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
(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
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() &
> 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 *
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
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
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
7 matches
Mail list logo
