random record

[genlight]

good day.

I have this table with id number and fullname.
Let say the table have 100,000 records.
I want to create a query that will show randomized select 20 records out of 100,000.

Can anybody show me how? I already researched on google but got no result. Thanks in advance.

[jmcilhinney]

The best way to do many things depends on the database, which you haven't told us.

[genlight]

oh, i forgot, im using mysql.

just now i saw a query but they said it will be slow if it involves million records

SELECT col1 FROM tbl WHERE RAND() limit 100;

[jmcilhinney]

Your table doesn't contain millions of records so no problem.

[genlight]

Your table doesn't contain millions of records so no problem.

that could be but supposing it has those number of records

[jmcilhinney]

Then the query will be slow.

[IkkeEnGij]

just a general idea how to select 20 random records out of 100000 or 2000000 or whatever at comparable speed:

do until there are 20 numbers stored
generate a random number between the lowest ID and the highest ID
if the generated number is an ID in the table, and not yet stored then store it
loop

now you have 20 distinct record id's

[ChrisE]

oh, i forgot, im using mysql.

just now i saw a query but they said it will be slow if it involves million records

SELECT col1 FROM tbl WHERE RAND() limit 100;

Hi,
try it this way...

$x = 20;
$sql = "SELECT Field1,
               Field2,
               Field3
        FROM table1
        ORDER BY RAND()
        LIMIT ".$x;
$query = mysql_query($sql);

this would be for Access...

SELECT TOP 20 Orders.OrderID, Orders.CustomerID
FROM Orders
ORDER BY rnd(ISNULL(Orders.OrderID) * 0 +1);

regards
Chris