Edit: https://gist.github.com/alecco/9976dab8fda8256ed403054ed0a65...

I think using a range of rows is overkill, at least for row-stores. And also in the majority of cases random rows are preferred than a range.

In the case where the table has a simple Primary Key the query is easier. Select all the valid PKs (rows) ordered by random and then limit.

SQLite gives access to the rowid making this query even simpler and likely faster (no need for PK and the query works on tables without a PK).

    SELECT * FROM test
        WHERE rowid IN
            (SELECT rowid FROM test
                ORDER BY random() LIMIT 10);

or with a more verbose JOIN:

    SELECT * FROM test JOIN
        (SELECT rowid as rid
            FROM test ORDER BY random() LIMIT 10) AS srid
        ON test.rowid = srid.rid;

The database engine tracks existing rows by some sort of id with its own internal rowid/PK index structure. Materializing these IDs should not be that expensive and as it's sequential access it should be pretty fast. The expensive part is the ORDER BY random().

If your table is truly big, say billions of rows, this could be improved by reducing the list of rowids with a WHERE clause.

But don't overdo it or you'll affect the truer randomness. For most cases just reduce to hundreds of thousands.

For whatever reason, using this filtered (WHERE), the JOIN query to generates a seemingly better SQLite plan.

    SELECT * FROM test JOIN
        (SELECT rowid as rid FROM test
            WHERE random() % 10 = 0  -- Reduce rowids
            ORDER BY random() LIMIT 10) AS srid
        ON test.rowid = srid.rid;

The manual '% 10' filter could be improved with some calculation of the table's row count, minding small tables. Left as exercise.