RE: Select a random record in SQL

I'm not sure about the duplicates. I've experienced that as well, but I
typically use something like 'INT(RAND() * 1000' or something like that so I
don't get that floating point number.

You may or may not be aware that each time you invoke the RAND() function,
you should be getting a different random number. So, if you specify RAND()
in the field list and RAND() in the order by, the random number returned in
the field list will not necessarily be the same as the random number used to
order the records. At least this has been my experience.

JJ



-----Original Message-----
From: rpg400-l-bounces+john=thejoiners.net@xxxxxxxxxxxx
[mailto:rpg400-l-bounces+john=thejoiners.net@xxxxxxxxxxxx] On Behalf Of Dan
Sent: Thursday, February 16, 2006 11:51 AM
To: RPG programming on the AS400 / iSeries
Subject: 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