Re: [sqlite] random rows

[Stephen Chrzanowski]

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