RE: Select a random record in SQL

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.

JJ

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Dan
Sent: Thursday, February 16, 2006 11:09 AM
To: RPG programming on the AS400 / iSeries
Subject: Re: Select a random record in SQL

On 2/16/06, Raul A. Jager W. <raul@xxxxxxxxxx> wrote:
>
> >>SELECT rand(), field1, field2,...,fieldn from MY/FILE
> >>    ORDER BY rand()
> >>Read the first 2000 (they will be randomly ordered)
> >>
> >>If you need aproximately 2000, you can use a "WHERE rand() < 0.02" and
> >>order by one of your fields
> >>
> >
> >Very nice.  In my test of this, I didn't see the RAND() in the Select and
> I
> >used:
> >    Select * from dblib1/cumas
> >      where ORPD > 20050101 and ORPD < 20050731
> >      order by RAND()
> >This gave what appeared to be a random list (the file is, physically, in
> >ORPD [Post Date] order).  Can I trust this?  Or should I specify RAND()
> in
> >the Select clause?  ("Select *, RAND() from..." won't work.)
> >
> >Thanks!
> >Dan
>
> The list will be in random order, but it will include all records
> entered between 20050101 and 20050731.
> Using rand() in the select will cause SQL to include randomly selected
> records from the whole file.


OK, I'm getting a bit confused.  Is there a difference between:
       Select cumas.*, rand() from cumas
         where ORPD > 20050101 and ORPD < 20050731
         order by RAND()
and:
       Select cumas.*         from cumas
         where ORPD > 20050101 and ORPD < 20050731
         order by RAND()

If not, why would one specify the rand() in the Select clause?

- Dan