RND function in SQL?

[addstravel]

I am attempting to insert a random number into a sort field in a temporary table.

I am using this in a stored procedure:

DECLARE @max int
select @max = count(ProfileId) from #tempresults

update #tempresults
set Sort = Rand(@Max)
where Sort = 0

I set the Sort field a default value of 0 and so if no sort value exists I wish to update the value with a random number. Does this make sense?

Anyone any ideas?
Cheers

[imbiz2001]

This is horrible - let me tell you, I know!

The site I work for, http://www.ienhance.com http://www.plasticsurgery.com http://www.cosmeticsurgery.com needed a similar function to randomize the photo gallery.

The problem is that RAND isn't really random.

If you run it a hundred times in a row, you'll get dozens of duplicates.

VB has a neat function, RANDOMIZE that you instantiate before RAND and it clears the buffers.

But you can't use it in a query.

Take a look at this, and see what you think:

[VBS]
declare @xx bigint
declare @limit bigint
set @limit = (select max(blahblah) from blabla)
set @xx = (select min(blahblah) from blabla)
while @xx <= @limit
begin
update blabla set placement = (@xx * @rndm) % @limit where blahblah_id = @xx
set @xx = @xx + 1
end

[/VBS]

In general, few duplicates exist, and since it only orders the photos in the gallery, if there are duplicates, it hardly matters, since it would only place them on the same page anyway.

This is just an idea generator, and shows one possible solution to a similar problem.

But RAND in SQL isn't really random. It's a very difficult question.

[imbiz2001]

OOPS!

forgot important part -

@rndm is a variable input by the call to the stored procedure from a VB6 EXE that uses the RANDOMIZE and RAND functions of VB6

This is only to ensure that the gallery is different from day to day.

it would work fine like so

[VBS]declare @rndm bigint
set @rndm = (RAND(@seed)*100000)
[/VBS]

and since the pseudo-random number in the while loop is also a function of the mod of it and two other numbers, there is little chance of duplication.

This EXE runs every night to shake up the gallery some.

[Mytch2001]

SQL is bad for random numbers...i usually create a proc and pass in the randomly seeded value as an input parameter.

[addstravel]

Have found this to work reasonable well. Although as you guys have stated not always 100% random but not bad:

CREATE PROCEDURE usp_SearchProfile
@PostCode nvarchar(3),
@Sort nvarchar(20),
@Dir nvarchar(10)

AS


select profile.ProfileId, profile.CompanyName as [Company Name], profile.Address as [Address], profile.postcode as [Postcode] , profile.Sort
into #tempresults
from profile
inner join postcodes on profile.profileid = postcodes.profileid
where postcodes.postcode = @PostCode and Show = 1


update #tempresults set Sort = 11
where Sort = 0

select * from #tempresults order by sort asc ,rand(datepart(ms, getdate()) * profileid)


This will random all those records without a sort order so it is possible to dictate a 'prefered' top profile...

what do you think? Good enough...