Re: Randomize data from SQL statement

Tricia Kha wrote:
>
> Hi,
>
> I want to generate a 30% random sample output data from my Order_number field
> of my Order_item table. Is there any technique or function that I could use?. I
> would like it to be done in SQL Statement not in PL/SQL.
>
> Thanks
> Tricia Kha
> tkha_at_accesscom.com

A hashing function, if it is working well, will return a uniformly-distributed number based on your order number. This will not be random but may be what you need as it should produce a well-dispersed sample. As hashing functions are sensitive to their input, you need to consult a textbook on algorithms, such as Knuth.

An alternative would be to use a general-purpose random-number generator. Again, these are tricky beasts. How random do you want your subset to be?

Anything that works is almost bound to use aspects of SQL which are not standard as SQL was not designed for the kind of computations you need.

Probably the best way would be to use the correct tool for the job: a statistical package such as SPSS or SAS. These will contain good random number and case selection tools.

-- 
Peter Lancashire
Information Systems Specialist, Bayer plc
Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK
Tel: +44-1635-562258,  Fax: +44-1635-562281
---
If all else fails, read the instructions AND the release notes.
All opinions are my own and not those of Bayer plc.
My Internet plumbing does not allow me to mail and post news together.
Sorry.
---
Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/