Re: Select a random record in SQL

On 2/16/06, John Joiner <john@xxxxxxxxxxxxxx> wrote:
>
> The difference is that your first example will contain a random number in
> the field list as well as be randomly ordered.
>
> Your second example will only be randomly ordered.


<sigh> O.k., I understood that.  ;-)

I was getting the impression from Raul's last message that having the rand()
in the select clause would cause all rows to be randomly selected _before_
the where clause restricted the rows to the date range specified.  Something
like 1) create a full subset of rows, randomly mixing them*, 2) selecting
only those randomly mixed rows by the date range in the where clause into a
second, smaller subset, and 3) via the "Order By rand()" clause, randomizing
the rows in the smaller subset.

* "Using rand() in the select will cause SQL to include randomly selected
records from the whole file."

I guess I misunderstood.

BTW, is there a reason why, when I have rand() in the select clause, I am
getting duplicate values in the rand() column?  The rows retrieved are
different and are not anywhere near being contiuous in physical sequence.
(FWIW, this is interactive SQL.)

      POST                   RAND ( )
      DATE
20,040,707    0.0000000000000000E+000
20,040,524    0.0000000000000000E+000
20,040,213    0.0000000000000000E+000
20,040,402    3.0518509475997192E-005
20,040,708    3.0518509475997192E-005
20,040,210    3.0518509475997192E-005
20,040,315    3.0518509475997192E-005
20,040,513    6.1037018951994385E-005
20,040,604    9.1555528427991577E-005
20,040,422    9.1555528427991577E-005
20,040,727    9.1555528427991577E-005
20,040,202    9.1555528427991577E-005
20,040,112    9.1555528427991577E-005
20,040,122    1.2207403790398877E-004
20,040,604    1.2207403790398877E-004
20,040,127    1.2207403790398877E-004
20,040,304    1.2207403790398877E-004
20,040,209    1.2207403790398877E-004

Thanks,
Dan