[thelist] Random value between 1 and x with SQL Server 2K

[Phil]

>"Luther, Ron" <Ron.Luther at compaq.com> pointed out:
>Hi Guy,
>
>FWIW - You may be correct.  Not all "random" functions are!  
>
>RonL.
>
>(Who, once upon a time, tested the seven different "random" functions that
>came with the standard Fortran package on a Honeywell mainframe ... and
>found that only two were actually "random".  {Three of us did the testing;
>one of us had a Master's degree in Math, the other two had Master's degrees
>in Statistics -- we knew "random"!})
>
Good Point, this "random output matching result" code below is not
delimited by using a predefined numeric limit. After opening the recordset
connection from a posted (form) query:

<%
sq = "SELECT TOP 25 mydatabase.IDms, mydatabase.State, mydatabase.Name,
mydatabase.[Phone Number], mydatabase.City, mydatabase.Url,
mydatabase.Email, mydatabase.C80, mydatabase.Profile FROM mydatabase  WHERE ("
sql = ""
qand = ""
sql = sql & qand & "((mydatabase.C80)=True) "
qand = " AND "

    If Request.QueryString("State")<> "*" Then
       sql = sql & qand & "((mydatabase.State)='" &
Request.QueryString("State") & "')"
       qand = " AND "
    End If
    
    If Request.QueryString("[MS]![C01]") = "yes" Then
       sql = sql & qand & "((mydatabase.C01)=" &
Request.QueryString("[MS]![C01]") & ")"
       qand = " AND "
    End If

<!-- long list of more conditional (row) vs QueryString if's -->

    If Request.QueryString("[MS]![C64]") = "yes" Then
       sql = sql & qand & "((mydatabase.C64)=" &
Request.QueryString("[MS]![C64]") & ")"
       qand = " AND "
    End If
s = sql
sql = sq & sql & ")  "
    If cstr(Param) <> "" And cstr(Data) <> "" Then
       sql = sql & " And [" & cstr(Param) & "] = " & cstr(Data)
    End If
If s <> "" Then
   sql = sql & " ORDER BY Rnd(Now() / mydatabase.IDms)    "
   Set rs = Server.CreateObject("ADODB.Recordset")
   rs.Open sql, conn, 3, 3
End If
%>

<%
On Error Resume Next
rs.MoveFirst
do while (Not rs.eof) and s <> ""
%>


Since sq is an array created by the matchups, containing only them, there
is no need to make a new, linear evaluation (array) of the results. Simply
harvesting the matches and then doing it this way results in a random
listing of output matched rows based on the Rnd() seeded by Now() against
the nearest IDms (table index row value) from among (only) the sq array of
harvested matches.

Unless you want to log/encode/archive/save the random results, I don't see
the reason why you would want to use random against a whole, newly
(re)ordered array, and even then, I don't see why you'd want to (re)stack
them.. (continuous range of numbers)  In other words, using the erratic
harvested row ID's further randomizes the results of the Rnd() function.

Just a thought..

Phil Stark
webdisplays