Can I select more rows than the table contains?

Question

I have a table that contains 100 rows. I want to select 200 elements from it, using random rows to generate more results than the table has rows for:

SELECT * FROM `rows` ORDER BY RANDOM() LIMIT 200; 

This query predicts 100 results. Is there a way to randomly select more than is actually contained in the table?

EDIT

Is there a way to select an arbitrary number of records without adding recipe join operators? For example, what if the requested counter ( LIMIT ) of elements is not known in advance or has an arbitrarily large size?

Answer 1

try something like this

 SELECT * FROM `rows` cross join `rows` ORDER BY RANDOM() LIMIT 200; 

Answer 2

One option:

 Select * From ( SELECT * FROM `rows` UNION ALL SELECT * FROM `rows` ) as alotofrows ORDER BY RANDOM() LIMIT 200; 

Answer 3

You should be able to do something like:

 select * from rows union all select * from rows order by random() limit 200 

Note that a 200 limit is not really required if you can guarantee that the table contains 100 rows.

union all gives you two copies (without duplication of deletion) and order by then ranks that the complete data set is for you.

Of course, if your table contains 100 and you need more than 200, you will need to apply a few more union proposals.

However, it may seem easier (and more flexible) to simply select 100 rows, bring them all into a structure in memory, and then choose which rows you want with code, not SQL.

This will allow you to control the selection process, of course, more than any "SQL gymnastics" that we are trying to write for you :-)

Answer 4

My approach does not satisfy the "arbitrarily large" requirements, but it scales easily.

The first problem is to generate N lines. In SQLite, I used views. You may also consider using virtual tables.

 CREATE VIEW V10 AS SELECT 0 AS Number UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9; CREATE VIEW V100 AS SELECT V10.Number*10+low.Number AS Number FROM V10 JOIN V10 AS low; CREATE VIEW V10000 AS SELECT V100.Number*100+low.Number AS Number FROM V100 JOIN V100 AS low; 

Request V10000 will return Number from 0 to 9999.

Now you can:

 SELECT table.* FROM table JOIN ( SELECT ( SELECT ROWID+Number*0 AS rndid FROM table ORDER BY RANDOM() ) FROM V10000 WHERE Number<200 ) ON table.ROWID=rndid; 

How it works?

• In most internal SELECT select one random row from table and return its ROWID named rndid ;
• An intermediate SELECT multiplies random strings;
• Outer SELECT matches integer table rows with rndid return.

If more than 10,000 results are required, another older VIEW can be created as the previous one.

Footnote: This is a pure SQL (ite) algorithm. Using some programming language, probably the most efficient would be to create a TEMP TABLE using repeating lines SELECT * FROM ORDER BY RANDOM() LIMIT 1;