Select Random Users

When testing, a common requirement is to select from a list of random Users (OPRID) or IDs (EMPLID). The following SQL provides a way to do this using:

  • PSOPRDEFN for a list of users (OPRIDs)
  • PS_PERSON for a list of people (EMPLIDs)

Note that this SQL is database specific.

Oracle

Select 100 random users (OPRIDs):

select  OPRID
from    
(
    select      OPRID
    from        PSOPRDEFN
    order by    dbms_random.value
) 
where rownum <= 100;

Select 100 random people (EMPLIDs):

select  EMPLID
from    
(
    select      EMPLID
    from        PS_PERSON
    order by    dbms_random.value
) 
where rownum <= 100;

Change the rownum <= 100 line to adjust the number of rows returned.

SQL Server

Select 100 random users (OPRIDs):

select top 100 OPRID
from PSOPRDEFN
order by NEWID();

Select 100 random people (EMPLIDs):

select top 100 EMPLID
from PS_PERSON
order by NEWID();

As usual, the Oracle syntax is far more cumbersome!

See this article on ideas on how to change this SQL for other database platforms such as DB2.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License