[RESOLVED] random selection of database query results

[jme]

Im sure that there's a really simple answer to this...

I have run a sql query that for example gives 100 results, but I only want to print 5 of these results randomly so that each page view will give a different set of results based on the 100 from the sql query.

Any suggestions please?

Jamie 🙂

[Drakla]

SELECT whatever FROM table ORDER BY RAND() LIMIT 5;

[LoganK]

I never knew you could do that right in SQL! I always loaded it into an array and used mt_rand();

[laserlight]

It may be better to run a:
SELECT COUNT(*) AS c FROM tablename

Then use say [man]mt_rand/man to obtain an integer r in the range [0,c)

After that, run:
SELECT columns FROM tablename LIMIT r, n
where n would be the number of rows required.

This is in view that RAND() may have to be evaluated for each row in the table, which is something of a performance penalty. Furthermore, if you use MySQL, the MySQL docs claim that "RAND() is not meant to be a perfect random generator, but instead a fast way to generate ad hoc random numbers that is portable between platforms for the same MySQL version."

Not only that, but if you use Mersenne Twister, this means you get more control over the pseudorandom number sequence.

[jme]

Thanks guys I'll look into both of them.

Jamie 🙂