Ever use RAND() successfully??

I want to randomly order a recordset of students from a student demographic table.

Then I want to randomly order those same students in another query in the exact same random order

RAND() stinks - nothing like VB RND...

Code:

---

SELECT RAND(),* from sometable

---

gives a column of random values that are all the same - RAND() must be evaluated prior to the SELECT and the value populated throughout the recordset. What a useless function...

You have to use your imagination my friend :)

This works:

Code:

---

create view RandView
as select rand() as RandomValue
go


CREATE FUNCTION dbo.ReturnRand()
RETURNS real
AS
BEGIN
 declare @RandomValue real
 set @RandomValue = (select RandomValue from RandView)
 return @RandomValue
END
GO

select dbo.ReturnRand(), * from sometable
GO

---

Quote:

---

Originally Posted by szlamany

I want to randomly order a recordset of students from a student demographic table.

---

One way to do that is by using the NewID() function.

try this:

Code:

---

SELECT * FROM SomeTable ORDER BY NewID()

---

Quote:

---

Originally Posted by kaffenils

You have to use your imagination my friend :)

This works:

Code:

---

create view RandView
as select rand() as RandomValue
go


CREATE FUNCTION dbo.ReturnRand()
RETURNS real
AS
BEGIN
 declare @RandomValue real
 set @RandomValue = (select RandomValue from RandView)
 return @RandomValue
END
GO

select dbo.ReturnRand(), * from sometable
GO

---

---

I'll give that a try when I get to work - I was headed in that direction last night, but declared the function as INT, which of course killed the floating point values of RAND(). I was noticing that the SELECT off that type of function was very slow, though...

I'm also now considering (after sleeping on it!) creating a more "permanent" table of student ids - put into a random order, and then simply using this table for queries during the scheduling process, which can take several weeks to complete. Any new students can simply be inserted into this "random order" table if the id is found to be missing.

In order to get it to work - it was required to seed the RAND() with a constant before the query on the student table...

Code:

---

select rand(100)
select dbo.ReturnRand(), * from student_t order by 1
GO

---

Each run of this query gives the same consistent random order.

Thanks again.

Quote:

---

Originally Posted by Blade

One way to do that is by using the NewID() function.

try this:

Code:

---

SELECT * FROM SomeTable ORDER BY NewID()

---

---

Unfortunately that gives a different random order of students with each run of the query.