1

I am trying to select random rows in Sql Server using a seed. I a,m aware of the function newid(), however I couldn't find a way to use a seed with this function. I want to run a code such as:

select user_id from users;
go

The code above selects users not in a random manner, I need assistance to in changing it to select users randomly according to a seed. So that if I run the same select with the same seed, I will get the same users.

Thanks, Clint

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
user502490
  • 146
  • 1
  • 10

2 Answers2

1

This is from MSDN that would select number between 0 to 99 and approximately top 10 percent:

SELECT * FROM Table1
WHERE (ABS(CAST(
(BINARY_CHECKSUM(*) *
RAND()) as int)) % 100) < 10

So, if you want 0 to 19 then use 20 instead of 100 for example.

I have not so elegant solution but worth the try like:

SELECT DISTINCT user_id, user_name, RandVal  FROM (
SELECT CAST(RIGHT(CAST(RAND(user_id)*100 as varchar(100)),3) as int) as RandVal,    user_id, user_name
FROM users) T
WHERE RandVal BETWEEN 700 and 900

See my Fiddle Demo

I used user_id as the seed.

And this one also:

SELECT TOP 5 user_id, MAX(user_name), MAX(RandVal) as RandVal FROM (
SELECT CAST(RIGHT(CAST(RAND(user_id)*100 as varchar(100)),2) as int) as RandVal,     user_id, user_name
FROM users) T
GROUP BY user_id
ORDER BY RandVal

See my demo for this one also.

Edper
  • 9,144
  • 1
  • 27
  • 46
  • Thanks ... but where do I specify the seed ? My purpose is to run the select twice and get the same random numbers. – user502490 Jun 24 '13 at 06:50
  • @user502490 - you would pass it inside `RAND()` - but note that this now depends on nothing changing in the table at all, and even then I'm not sure that it's 100% reliable. – Damien_The_Unbeliever Jun 24 '13 at 06:55
  • You could put value inside the RAND() function like RAND(100). – Edper Jun 24 '13 at 07:01
0

Try this to get 10 random users: 

SELECT user_id From users WHERE rand()>0.5 limit 10;
vp_arth
  • 14,461
  • 4
  • 37
  • 66