Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 403
  • Last Modified:

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?
0
arthurh88
Asked:
arthurh88
1 Solution
 
Dave BaldwinFixer of ProblemsCommented:
No, that won't work.  The ORDER BY needs to be a field in the SELECT-ed output.  

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
0
 
Angelp1ayCommented:
SELECT * FROM table
ORDER BY NEWID()

Open in new window


Edit: Ahh sorry I posted too quickly - didn't see the bit about the seed.
0
 
QuinnDexCommented:
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.
--1 to 6

ABS(Checksum(NewID()) % 5) + 1

-- 20 to 40

ABS(Checksum(NewID()) % 40) + 20

Open in new window

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Angelp1ayCommented:
This should work :) Tested locally and it works for me.

DECLARE @seed as int = 10;

SELECT *, r = HASHBYTES('md5', CAST(PrimaryKeyCol + @seed as varchar))
FROM YourTable
ORDER BY r

Open in new window


I have an integer primary key but I think as long as you can consistently produce as varchar from a unique column in your table plus the seed (which in this case is more like a salt) then it should work in all cases.
0
 
Chris LuttrellSenior Database ArchitectCommented:
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.
0
 
arthurh88Author Commented:
wow. that worked for me too.   thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now