Random samples 1

[Paranoia99]

I have a table of invoice numbers that I would like to pull a random sample (of say 1000 invoices) from to evaluate for errors or discrepancies. Is there a way to pull a random sample straight from the SELECT statement?

I know I could make a table with just the list of random invoice numbers first, then do a nested subquery on that table, but I was hoping for something a little more elegant.

 

[Mike Gagnon]

You could use :
SELECT * form myTable where BETWEEN(recno(),1,1000)

And chage the recno() range if you want to choose another sample.

 

[Mike Gagnon]

You could use :
SELECT * from myTable where BETWEEN(recno(),1,1000)

And chage the recno() range if you want to choose another sample.

 

[Mike Gagnon]

You could use :
SELECT * from myTable where BETWEEN(recno(),1,1000)

And change the recno() range if you want to choose another sample.

 

[baltman]

select top 1000 rand() as randnum,* order by 1 desc from mytable

If you want to show how random the numbers really are, you'd need to do something like:

select top 1000 (rand()+.00000000) as randnum,*;
order by 1 desc from mytable

Becase otherwise VFP will round the random number to 2 decimals.

Brian

 

[Paranoia99]

Thanks a lot for the help mgagnon and baltman. Those were exactly the type of responses I was looking for.