SQL Server CE - Select random rows

Question

How do you select a random rows from a table?

For example, if there are 1000 rows in a table matching the criteria that I want, I want to select just 20 random ones.

Like TOP, but random.

Thanks, and this is for SQL Server CE 3.5!

Answer 1

How about this?

 SELECT TOP(20) * FROM myTable ORDER BY NEWID()

Answer 2

How about SELECT * FROM tbl_name ORDER BY RAND() LIMIT 0,20; However if your db is with million rows Both newid() and rand() would perform slow. There is a faster solution .Read this