[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