Thursday, December 26, 2019

Selecting fixed number of random rows in the results

using sql rand to a number of records from a file at random

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.

No comments:

Post a Comment

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.