[Gllug] Perl DBI question

[Russell Howe]

On Mon, Oct 02, 2006 at 10:54:46PM +0100, Adrian McMenamin wrote:
> Maybe more appropriate for a perl list...
> 
> Given a database of N records where N > 0 but size of N is otherwise
> unkown, is it possible to pick a random record from the database?

How about

SELECT r1.* FROM records AS r1
WHERE CEILING((SELECT COUNT(*) FROM records) * RANDOM())
        = (SELECT COUNT(*) FROM records AS r2 WHERE r1.record_id <=
	r2.record_id);

(assuming 'record' has a numeric primary key called 'record_id')

The randomness of this will depend on the distribution of primary key
values. If it isn't even (say there are a lot of 'gaps' in the lower
values, as old rows have been removed, then this will give preference to
those entries with a higher value for 'record_id').

I think the other responses may well be RDBMS-specific, and probably
much more efficient than a 'pure SQL' version.

Also, note that RANDOM() sometimes isn't all that random depending on
your RDBMS. I'd check the coverage of results for your particular
application.

-- 
Russell Howe       | Why be just another cog in the machine,
rhowe at siksai.co.uk | when you can be the spanner in the works?
-- 
Gllug mailing list  -  Gllug at gllug.org.uk
http://lists.gllug.org.uk/mailman/listinfo/gllug