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?
Last Comment
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) + 20
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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