How to select N random rows using pure SQL?

Question

How do we combine http://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql and http://stackoverflow.com/questions/183124/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.

Answer 1

The answer to your question is in the second link there:

SELECT * FROM table ORDER BY RAND() LIMIT 1

Just change the limit, and/or rewrite for SQL Server:

SELECT TOP 1 * FROM table ORDER BY newid()

Now, this strictly answers your question, but you really shouldn't be using this solution. Just try it on a large table and you'll see what I mean.

If your key-space is sequential, either without holes, or with very few holes, and if it has very few holes, you're not too concerned that some rows have a slightly higher chance of being picked than others, then you can use a variation where you calculate which key you want to retrieve randomly, ranging from 1 to the highest key in your table, and then retrieve the first row that has a key equal to or higher than the number you calculated. You only need the "higher than" part if your key-space has holes.

This SQL is left as an excercise for the reader.

---

Edit: Note, a comment to another answer here mentions that perhaps pure SQL means ANSI standard SQL. If that is the case, then there is no way, since there is no standardized random function, nor does every database engine treat the random number function the same way. At least one engine I've seen "optimizes" the call by calling it once and just repeating the calculated value for all rows.

Answer 2

I don't know about pure ANSI, and it's not simple, but you can check out my answer to a similar question here: http://stackoverflow.com/questions/249301/simple-random-samples-from-a-mysql-database