SELECT random value from another table

Database
New Member

SELECT random value from another table

Hi!

 

I have a main table EMPLOYEES, which amongst other columns, contains emp_type column. Emp_Type value list is stored to EMPLOYEE_TYPES table.

I need to create a view on top of EMPLOYEES table. Most view values are the same as in EMPLOYEES  but instead of showing emp_type as it's stored in EMPLOYEES, I need to show those values randomly - basically shuffle EMPLOYEES.emp_type column up or select random value from it's source table EMPLOYEE_TYPES.type_code.

Example data:

EMPLOYEES table: 

Employee_Id         emp_type

1                                 6

2                                 9d

3                                 a1

 

EMPLOYEE_TYPES table:

type_code

a1

6

d_8

9_d

...

Desired result (emt_type is random):

Employee_Id       emp_type

1                               d_8

2                               9_d

3                               6

 

How I tried to do it, is to create an Id for the EMPLOYEE_TYPES table and join to it with a randomly generated Id value.

But this solution is not really suitable as I had to insert the upper_bound value for  RANDOM ( lower_bound, upper_bound ) manually (85 is the number of records in EMPLOYEE_TYPES table). And I need it to be dynamic.

 

SELECT

e.employee_id,
e_type.emp_type
FROM EMPLOYEES e
LEFT JOIN (
SELECT type_code,
Row_Number() Over (ORDER BY type_code) type_id
FROM EMPLOYEE_TYPES 
e_type on e_type.type_Id = random (1, 85);

 

Could you please help me solve it?!

1 REPLY
Highlighted
Junior Contributor

Re: SELECT random value from another table

Calculate a high large random number and apply Modulo:

SELECT
e.employee_id,
e_type.emp_type
FROM EMPLOYEES e
LEFT JOIN (
SELECT type_code,
Row_Number() Over (ORDER BY type_code) type_id
FROM EMPLOYEE_TYPES 
) e_type on e_type.type_Id = (random (1, 10000000)  mod (select count(*) from employee_types)) +1