[thelist] SQL select help

rudy r937 at interlog.com
Thu Jul 26 19:08:32 CDT 2001


>  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






More information about the thelist mailing list