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!

score 2 · Accepted Answer · answered Apr 01 '12 at 05:29

2

How about this?

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

answered Apr 01 '12 at 05:29

BluesRockAddict

15,525
3
37
35

@Mitch - that's weird, unfortunately I don't have 4.0 to try it on at the moment. – BluesRockAddict Apr 01 '12 at 05:46

Milindu Sanoj Kumarage · Answer 2 · 2012-04-01T06:15:49.227

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

edited Apr 01 '12 at 06:15

answered Apr 01 '12 at 06:06

Milindu Sanoj Kumarage

2,714
2
31
54

If the query I end up making is too slow I will have to take a look at that. Thanks for posting. – Meowbits Apr 01 '12 at 06:18
RAND() is for MySQL, the question is about SQL SERVER CE. – Jone Polvora Feb 23 '15 at 21:58

SQL Server CE - Select random rows

2 Answers2

Linked