SQL server getting random number of random rows

Question

Is there a way to get random N rows from SQL server table? I saw this link, but that is not what I want. I want to get different number of rows each time I run the query - for example, 5 rows, 10 rows etc

At least you need to add the ordering, otherwise you'd be getting the same rows, although different amount of them. — GSerg, Oct 09 '20 at 17:04

score 1 · Answer 1 · answered Oct 09 '20 at 16:35

1

You could use something like this:

select t.*
from (select t.*, row_number() over (order by (select null)) as seqnum
      from t 
     ) t cross join
     (select count(*) as cnt, rand() as rnd from t) x
where seqnum < cnt * rnd;

answered Oct 09 '20 at 16:35

Gordon Linoff

1,242,037
58
646
786

score 0 · Accepted Answer · answered Oct 09 '20 at 21:22

0

GSerg wrote the following comment:

select top (cast(rand() * 10 as int)) * from table

answered Oct 09 '20 at 21:22

Peter O.

32,158
14
82
96

Although there is no guarantee as to which rows are returned when using `top` without `order by`, this hardly seems to return a "random number of _random_ rows". – HABO Nov 13 '20 at 01:58

SQL server getting random number of random rows

2 Answers2