How to select N random rows using pure SQL?

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.

I don't know about pure ANSI, and it's not simple, but you can check out my answer to a similar question here:Simple Random Samples from a Sql database

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.