[thelist] SQL select help

Ed Courtenay ed at edcourtenay.co.uk
Fri Jul 27 04:31:23 CDT 2001


The problem with this method is that it relies on the identity field being
sequential - something you can't always be certain of.

An extension to this idea would be to create a stored procedure that creates
a temporary table of the result set, that appends its own sequential ID and
uses that to do the random sort.

Depending on the size of your result set (and assuming you're using ADO) it
might be simpler to do the following:

' ASP example:
<script runat="server">
Function RandRange(min, max)
    Randomize
    RandRange = Int((max - min + 1) * Rnd + min)
End Function
</script>

<%
    ...
    ' Select results into oRS...
    ...
    oRS.Move RandRange(0, oRS.RecordCount - 1)
%>

----- Original Message -----
From: "rudy" <r937 at interlog.com>
To: "evolt thelist" <thelist at lists.evolt.org>
Sent: Friday, July 27, 2001 1:08 AM
Subject: Re: [thelist] SQL select help


> >  I am using SQL Server 2000.
>
> great, then you can use    rand()
>
> note this function returns a decimal number between 0.0 and 1.0
>
> what you want to do is generate a number between the lowest actual key and
> the highest actual key
>
> you can assume an identity field was declared with default seed 0 and
> increment 1, but just to be sure, check it out, because it will affect the
> following formula
>
> the lowest possible key is   seed + incr   while the highest actual key
> would be obtained by the  max() function
>
> you don't want a decimal number, so that means using the floor(),
> ceiling(), or round() function to get the nearest integer
>
> so it would be something like
>
>   select * from yourTable
>     where yourTable.ID =
>       ( select min(primaryID) from yourTable
>          where primaryID >=
>              floor(   rand()
>                        * ( select max(primaryID) from yourTable )
>                     )
>       )
>
> warning:   not tested
>
>
> rudy
>
>
>
> ---------------------------------------
> For unsubscribe and other options, including
> the Tip Harvester and archive of TheList go to:
> http://lists.evolt.org Workers of the Web, evolt !





More information about the thelist mailing list