SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist
    Join Date
    Jun 2001
    Location
    London
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    RND function in SQL?

    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

  2. #2
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Location
    Salt Lake City Utah
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Location
    Salt Lake City Utah
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SQL is bad for random numbers...i usually create a proc and pass in the randomly seeded value as an input parameter.
    Get ConMan and run your own web site!

    Want free programming eBooks? http://www.devarticles.com/ebooks.php

  5. #5
    SitePoint Evangelist
    Join Date
    Jun 2001
    Location
    London
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •