database for random selection

Question

I wanted to be able to randomly select rows from a table in a way that scales up to large tables in MySQL. As I understand it now that really isn't possible. Are there any other databases that I could do this with?

Answer 1

Try this:

SELECT ColumnName 
FROM TableName
ORDER BY RAND()
LIMIT 1;

Answer 2

Does this type of selection need to be made with SQL? If not, you could generate a random number in code and then get a related record by the generated number. Of course that is making a few assumptions:

• Would require that your Id's are an identity seed
• your random number is bounded by min & max values.

Answer 3

I think this can be done... Sorry -- this is TSQL, but FWIW

Declare 
   @max int, 
   @min int, 
   @rand int

set @max = (select max(id) from myTable)
set @min = (select min(id) from myTable)
set @rand = Cast(((@max + 1) - @min) * Rand() + @min as int)

select * from myTable where id = @rand

Or if you've got gaps in your primary key fields, then:

select max(id) from myTable where id <= @rand

Per @andrew's objection -- he's concerned that this would favor lower id numbers in un-matched contests. If that's a concern, a couple of ways you could handle it:

Take 2 records at a time (a min and a max). For example:

INSERT INTO whatever_table_variable_etc
select max(id) from myTable where id <= @rand
UNION
select min(id) from myTable where id >= @rand

Or you could base it on odds and evens. For example:

Declare @isOdd bool
SET @isOdd= CASE WHEN ABS(@rand) % 2 = 1 THEN 1 ELSE 0 END

If @isOdd = 1
   select max(id) from myTable where id <= @rand
ELSE 
   select min(id) from myTable where id >= @rand