Alex Rivera | Logout

How to select N random rows using pure SQL?

Asked 2008-12-29T01:20:27.843
17

How do we combine How to request a random row in SQL? and Multiple random values in SQL Server 2005 to select N random rows using a single pure-SQL query? Ideally, I'd like to avoid the use of stored procedures if possible. Is this even possible?

CLARIFICATIONS:

  1. Pure SQL refers to as close as possible to the ANSI/ISO standard.
  2. The solution should be "efficient enough". Granted ORDER BY RAND() might work but as others have pointed out this isn't feasible for medium-sized tables.
sql random
Edit
Report

1 Answer

2

Here's a potential solution, that would let you balance the risk of getting less than N rows against a sampling bias from the "front" of the table. This assumes that N is small compared to the size of the table:

select * from table where random() < (N / (select count(1) from table)) limit N;

This will generally sample most of the table, but can return less than N rows. If having some bias is acceptable, the numerator can be changed from N to 1.5*N or 2*N to make it very likely that N rows will be returned. Additionally, if it's necessary to randomize the row order, not just select a random subset:

select * from (select * from table
                where random() < (N / (select count(1) from table)) limit N)
 order by mod(tableid,1111);

The downside of this solution is that, at least in PostgreSQL, it uses a sequential scan of the table. A larger numerator will speed up the query.

answered 2013-03-20T15:50:35.733

Your Answer