arthurh88
asked on
can SQL do an Order By RND (KEY) ?
I want to order my select as random, but i want the same random each time. for example, i'd like to do an Order by Rnd(KEY) so that if i change the KEY i get a new random result, but if the key is the same, the result is the same. Possible?
SELECT * FROM table
ORDER BY NEWID()Edit: Ahh sorry I posted too quickly - didn't see the bit about the seed.
not possible, you can constrict the rand() to give you numbers in the same range but they wont come out in the same order each time the are run, so your orderby will change each time its run
if its constricting the order by to a range that you want then this should do it.
if its constricting the order by to a range that you want then this should do it.
--1 to 6
ABS(Checksum(NewID()) % 5) + 1
-- 20 to 40
ABS(Checksum(NewID()) % 40) + 20ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No, the value for each occurrence of RAND() or RAND(x) for any seed value of x in a select statement gives the same value for every row in the same statement.
You can get a random ordering by " ORDER BY NEWID()" but it cannot take a seed so it will always be different, sorry.
You can get a random ordering by " ORDER BY NEWID()" but it cannot take a seed so it will always be different, sorry.
ASKER
wow. that worked for me too. thank you
http://msdn.microsoft.com/en-us/library/e9zc0283%28v=vs.80%29.aspx
You might be able to do
SELECT RAND(1000) AS randomnumber.... ORDER BY randomnumber
but I haven't tried that. Calling it that way may just reseed it each time and not provide a random number.
http://technet.microsoft.com/en-us/library/ms177610.aspx