Selecting fixed number of random rows in the results

I have written about using SQL's RAND() to generate random numbers to be included in the returned results, but not to return a fixed number of randomly selected results. In other words I just want five randomly selected rows returned in the results.

In this example I have a file, and yes it is a DDS file, that contains just two fields:

• COMPANY: company number
• VALUE: a three long numeric field

For company number 1 there are twenty records with the sequential values 1 – 20.

The request is for, let's say, five randomly selected records for company number 1 only.

I could create a table from the results with a random number column in it, and then select the first five rows. But that is a two step process, why use that when I get what I want in just one step?

01 SELECT * FROM QTEMP.TEMPFILE 02 WHERE COMPANY = 1 03 ORDER BY RAND() 04 LIMIT 5

Line 1: All the fields/columns from the file are returned in the results.

Line 2: I only want to include the records/rows for company number 1.

Line 3: By using the RAND() it means that the each record/row is assigned a random number, and by using the ORDER they are sorted by that generated random number. And I don't care what those random numbers are.

Line 4: The LIMIT restricts the number of returned result to the number given, in this case just five results.

The first time I ran the statement my results were:

COMPANY VALUE ------- ----- 1 2 1 14 1 12 1 20 1 11

The next time:

COMPANY VALUE ------- ----- 1 6 1 4 1 11 1 1 1 13

As there are only 20 possible results I am going to see the same numbers appear often in the results.

 

This article was written for IBM i 7.4, and should work for some earlier releases too.