Good idea but if there is no AUTOINCREMENT you can simply get the min/max
directly:
I think the following might work (and should work for both + and - rowid's)
with Rows(MinRowID, MaxRowID, NumRows) as (select min(RowID) as MinRowID,
max(RowID) - min(RowID) + 1
as NumRows
from tab),
Potential(Candidate) as (select abs(random() % (select NumRows from
Rows)) + (select MinRowID - 1 from Rows) as Candidate
from generate_series
where start=1
and stop=(select 2*NumRows from Rows))
select distinct tab.*
from Potential
cross join tab
on tab.RowID == Potential.Candidate
limit 200;
NB: dependancy on extension series.c -> generate_series
Selecting 200 random rows from tab containing 1 million rows takes 0.2
seconds ...
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Hick Gunter
>Sent: Friday, 1 June, 2018 12:09
>To: 'SQLite mailing list'
>Subject: Re: [sqlite] [EXTERNAL] Re: random rows
>
>Just an idea:
>
>If the table has an INTEGER PRIMARY KEY AUTOINCREMENT then then
>maximum rowid is available in the sqlite_sequence table.
>To generate a random sequence of rowids, this can be used, somewhat
>like
>
>SELECT DISTINCT rnd() * (SELECT seq FROM sqlite_sequence WHERE
>name='<table_name>') as rowid;
>
>Maybe as a view or a CTE.
>
>Then use this as the LHS of a join
>
>SELECT t.* from (...) random cross join <table_name> t on
>random.rowid = t.rowid LIMIT x;
>
>Creating random rowids should be blindingly fast, even with storing
>the already created rowids in a temp btree. Reading the table via
>rowid is as fast as possible. Any "misses" in the rowid generation
>(i.e. rowids not present) are compensated by the number of rowids
>generated. Speed increases with the desity of rowids (i.e.
><current_row_count>/<max_inserted_rowid>). Unless of course SQLite
>chooses to create all the rowids before providing the first one.
>
>-----Ursprüngliche Nachricht-----
>Von: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] Im Auftrag von Stephen Chrzanowski
>Gesendet: Freitag, 01. Juni 2018 18:47
>An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>Betreff: [EXTERNAL] Re: [sqlite] random rows
>
>Here's my two cents. Don't spend it all in one place...
>
>CREATE TABLE [RandomTable](
> [PriID] INTEGER PRIMARY KEY AUTOINCREMENT,
> [DataCol] CHAR);
>
>INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(1, 'a'); INSERT
>INTO [RandomTable]([PriID], [DataCol]) VALUES(2, 'b'); INSERT INTO
>[RandomTable]([PriID], [DataCol]) VALUES(3, 'c'); INSERT INTO
>[RandomTable]([PriID], [DataCol]) VALUES(4, 'd'); INSERT INTO
>[RandomTable]([PriID], [DataCol]) VALUES(5, 'e'); INSERT INTO
>[RandomTable]([PriID], [DataCol]) VALUES(6, 'f'); INSERT INTO
>[RandomTable]([PriID], [DataCol]) VALUES(7, 'g'); INSERT INTO
>[RandomTable]([PriID], [DataCol]) VALUES(8, 'h'); INSERT INTO
>[RandomTable]([PriID], [DataCol]) VALUES(9, 'i'); INSERT INTO
>[RandomTable]([PriID], [DataCol]) VALUES(10, 'j'); INSERT INTO
>[RandomTable]([PriID], [DataCol]) VALUES(11, 'k'); INSERT INTO
>[RandomTable]([PriID], [DataCol]) VALUES(12, 'l'); INSERT INTO
>[RandomTable]([PriID], [DataCol]) VALUES(13, 'm'); INSERT INTO
>[RandomTable]([PriID], [DataCol]) VALUES(14, 'n'); INSERT INTO
>[RandomTable]([PriID], [DataCol]) VALUES(15, 'o'); INSERT INTO
>[RandomTable]([PriID], [DataCol]) VALUES(16, 'p'); INSERT INTO
>[RandomTable]([PriID], [DataCol]) VALUES(17, 'q'); INSERT INTO
>[RandomTable]([PriID], [DataCol]) VALUES(18, 'r'); INSERT INTO
>[RandomTable]([PriID], [DataCol]) VALUES(19, 's'); INSERT INTO
>[RandomTable]([PriID], [DataCol]) VALUES(20, 't'); INSERT INTO
>[RandomTable]([PriID], [DataCol]) VALUES(21, 'u'); INSERT INTO
>[RandomTable]([PriID], [DataCol]) VALUES(22, 'v'); INSERT INTO
>[RandomTable]([PriID], [DataCol]) VALUES(23, 'w'); INSERT INTO
>[RandomTable]([PriID], [DataCol]) VALUES(24, 'x'); INSERT INTO
>[RandomTable]([PriID], [DataCol]) VALUES(25, 'y'); INSERT INTO
>[RandomTable]([PriID], [DataCol]) VALUES(26, 'z');
>
>select random() as R,* from RandomTable order by R limit 10;
>
>Using 3.23.1
>
>
>On Fri, Jun 1, 2018 at 10:22 AM, Don V Nielsen
><donvniel...@gmail.com>
>wrote:
>
>> ??
>>
>> SELECT * FROM table WHERE id IN (SELECT id FROM table ORDER BY
>> RANDOM() LIMIT x)
>>
>> Maybe. It is more memory efficient then trying to sort the entire
>lot
>> of data.
>>
>>
>> On Thu, May 31, 2018 at 7:13 PM Torsten Curdt <tcu...@vafer.org>
>wrote:
>>
>> > I need to get some random rows from a large(ish) table.
>> >
>> > The following seems to be the most straight forward - but not the
>> fastest.
>> >
>> > SELECT * FROM table ORDER BY random() limit 200
>> >
>> > Is there a faster/better approach?
>> >
>> > cheers,
>> > Torsten
>> > _______________________________________________
>> > sqlite-users mailing list
>> > sqlite-users@mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>> >
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>___________________________________________
> Gunter Hick | Software Engineer | Scientific Games International
>GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
>0430013 | (O) +43 1 80100 - 0
>
>May be privileged. May be confidential. Please delete if not the
>addressee.
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
