Random result between queries

user2354982 0
Viewed 48

The following queries have the same result, although they have different seed numbers:

SELECT * FROM Customers ORDER BY RAND();
SELECT * FROM Customers ORDER BY RAND(5);
SELECT * FROM Customers ORDER BY RAND(1290);
SELECT * FROM Customers ORDER BY RAND(0.121);

How do I write a query that returns the same result for a specific seed but a different result for a different seed? I want to keep the random order for the same seed.

sql-server sql
1 Answers

You need something unique and deterministic per row. I assume you have a CustomerId column below.

You can then use something like

SELECT * 
FROM Customers
ORDER BY HASHBYTES('SHA2_256', CAST(RAND(CHECKSUM(CustomerId, @Seed) AS binary(8)))

Or potentially generate a row numbering using your unique and deterministic value and combine this with your @Seed to produce an integer to use to seed RAND per row.

NB: Originally I tried

ORDER BY RAND(CHECKSUM(CustomerId) + @Seed)

But changing the @Seed changed the value but did not change the relative ordering of rows. It looks as though small adjustments to the seed just produce small adjustments in the random value correlated with the ordering of the seed (e.g. try SELECT RAND(6), RAND(7), RAND(8))

Wrapping with HASHBYTES will hopefully mitigate this and mean small adjustments in the value still lead to larger differences in the eventual value used for ordering.

userG4j0
Related