Re: Select a random record in SQL -- RPG400-L

I had understud that you needed to select 2000 random records from thewhole file, if so, using a "where" will limit the sample. If yor samplemust be from the records in that range, the where is ok.Under some circunstances SQL will not allow to sort by a column that isnot in the select, if not the case, you don't need to include the rand()in the select.


Probably you can get better performance using the "fetch-first" clause.
__________________________________________________________________________________________
Dan wrote:

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