Ever use RAND() successfully??

[szlamany]

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...

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...

[kaffenils]

You have to use your imagination my friend

This works:

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

[Blade]

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:

SELECT * FROM SomeTable ORDER BY NewID()

[szlamany]

You have to use your imagination my friend

This works:

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.

[szlamany]

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

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.

[szlamany]

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

try this:

SELECT * FROM SomeTable ORDER BY NewID()

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