Skip to content
Advertisement

Oracle SQL randomising column entries

I have the below table:

PC A MAXI
PC1 A1 1
PC1 A2 2
PC1 A3 3
PC2 A1 1
PC2 A2 2
PC2 A3 3

Is there a way to generate ‘MAXI’ entries for each group of ‘PC’ and ‘A’ in a random order (or at least a seemingly even distribution, even if not random)?

Desired output (count_pc would just be an order count over partition by PC):

PC A COUNT_PC
PC1 A2 1
PC1 A1 2
PC1 A3 3
PC1 A3 4
PC1 A2 5
PC1 A3 6
PC2 A3 1
PC2 A2 2
PC2 A3 3
PC2 A1 4
PC2 A2 5
PC2 A3 6

Advertisement

Answer

You can generate your rows using your favorite method, then use a random number generator for the last column (with row_number()):

with cte(pc, a, maxi) as (
      select pc, a, maxi
      from t
      union all
      select pc, a, maxi - 1
      from cte
      where maxi > 1
     )
select pc, a,
       row_number() over (partition by pc order by dbms_random.random) as count_pc
from cte
order by pc, count_pc;
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement