select random rows seed in sql server

问题:

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

回答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.

回答2:

Try this to get 10 random users:

SELECT user_id From users WHERE rand()>0.5 limit 10;