× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I had understud that you needed to select 2000 random records from the whole file, if so, using a "where" will limit the sample. If yor sample must be from the records in that range, the where is ok. Under some circunstances SQL will not allow to sort by a column that is not 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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.