• Oracle Database 11g Enterprise Edition Release - Production
  • PL/SQL Release - Production "CORE Production"
  • TNS for 32-bit Windows: Version - Production
  • NLSRTL Version - Production

I will base this question of the HR Schema for simplicity. I have two tables Employees and Locations. For this example, I have 37 locations and 173 employees.

My locations have a specific number assigned indicating the number of employees that need to be assigned to the location. What I am attempting to do is randomly assign the number of required employees for each location from the employees table.

As such an example of desired results would be:

1              A        Smith     737 Any Road Anywhere USA 
2              B         Jones     50 Some Road Nowhere USA 
3              C         Butler   737 Any Road Anywhere USA 
4              D         Thomas    50 Some Road Nowhere USA 
5              E         Marsh    737 Any Road Anywhere USA 
6              F         Ruppert   50 Some Road Nowhere USA 
7              G         Glenn     50 Some Road Nowhere USA 
  • What is your question? You may want to include table definitions and some sample data, in addition to your attempted SQL statement.
    – mustaccio
    Jul 5, 2017 at 17:11
  • Question was marked as answered in your thread on same at OTN forums - community.oracle.com/thread/4060777
    – EdStevens
    Jul 5, 2017 at 21:30

2 Answers 2


Your professor should have taught you the mod function. You can use rownumber and mod to come up with a number from 0 to n where n is one less than the number that you divide by. Hence mod(rownum, 37)+1 will give you a random number between 1 and 37. This gives you between 4 and 5 people working at every location. Sounds oddly random. Except it would not be random for a database class. This query should help you to get on track.

SELECT 'location_id_'||LPAD(mod(rownum, 10)+1, 3, '0') location_id,
       employee_id, first_name, last_name, street_address, city, state 
  FROM employee
 ORDER BY rowid;
  • Before you post answers, the expectation is that you read the problem statement carefully. The OP stated very clearly that each location has a specific number of needed employees. You seem to have completely missed that part.
    – mathguy
    Jul 5, 2017 at 22:28
  • Perhaps the OP could have shown the table structure for all of the tables or at least explained how someone would know how many people belonged in each location. Perhaps the OP could have given names for each location. The OP didn't do any of that. But I provided a starting point on how to solve the problem. Perhaps you can post your own code.
    – Gandolf989
    Jul 6, 2017 at 13:56
  • Gandolf989 - Just for FYI - I don't have a professor nor a database class. This was for a work issue. At 52 I am still learning. It would probably be best not to be so presumptuous, and perhaps you will find life much more interesting and YOU may even learn something yourself....
    – GMoney
    Aug 26, 2017 at 0:43
  • While it might be a work assignment, on the face of it it looks like something that someone would do in a class. Rowid is somewhat random, therefore it would be a reasonable way to guarantee a random and even distribution. Using a random function could easily put too many people in the same branch. Certainly having a computer program tell you which office to work in regardless of how far it is from your house would make life interesting.
    – Gandolf989
    Aug 28, 2017 at 13:59

Use the random function to generate a number. "Number" your locations with slots equal to the number of positions they need. For example, if location A needs 15 and location B 16 then request a random number from 1 to 31 and:

  • 1-15 go to A
  • 16-31 go to B

Save that result to a table, and then pull all employees assigned per site.

  • How do you use the RANDOM function to generate the numbers from 1 to 31, without duplicates and without gaps? (Hint: you can't; that would definitely not be RANDOM.)
    – mathguy
    Jul 5, 2017 at 22:30
  • You store them as you generate them and then skip the collisions near the end.
    – CalZ
    Jul 6, 2017 at 11:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.