SELECT 50 RANDOM RECORDS FROM TRANSACTION TABLE

[MJRBIM]

CR 8.5, Oracle 8i DB

I know this question has been answered before, but I am having all kinds of trouble accessing old "searched" posts with the new look Tek-Tips.

What I am trying to do is find the most efficent way of selecting 50 (fifty) random records from an ever growing Transaction table.

My guess is that we could take the ever changing total TRANSACTION record count divide it by 50 - and use that result (integer) as the increment for each of the records in the SELECT statement.


-------------------

eg. Total TRANSACTION record count = 174,583
Divided by 50 = 3,492

SELECT WHERE Transaction Record number is one of...
3,492
6,984
10,476
13,968
17,460
20,952
24,444
27,936
31,428
34,920...etc

-------------------

Is that the best way to do a RANDOM count? And if so, what is the correct Crystal Syntax to implement that logic?

Thanks in advance...

 

[lbass]

Ido Millet wrote an FAQ on this. Try faq767-3260.

-LB

 

[MJRBIM]

lbass -

Ido's approach returns all the rows in the table and conditionally supresses the results based on his formula.

That is not really what I am looking for - as my TRANSACTION TABLE could be huge, and I want to avoid returning all the rows in the first place.

I am trying to come up with a solution that does the filtering at the RECORD SELECTION level, and only selects 50 random TRANSACTION NUMBERS.

This may turn out to be something I have to do on the ORACLE side...

Any RECORD SELECTION suggestions would be appreciated....Thanks!

 

[lbass]

I SEE what you MEAN . Another approach would be to create a SQL expression {%random}:

{fn RAND(table.`field`)}

Add this to your report canvas and as a sort field. Then use it in your record selection formula:

{%random} <= .2

The problem with this is that the number of records returned will differ with each refresh, but you could probably determine what value to use instead of ".2" that would almost(?) always return at least the number of records you want, and then use a suppression formula to suppress the overage. With this approach, you might, e.g., have 57 records returned, so that you would need to suppress only 7, instead of thousands.

-LB