CODEWITHSUNDEEP
sqloracle-database
user2815115
user2815115

Reputation: 79

Random number based on a group and should not to equal to row number generated in Oracle

I have a requirement to Swap the records within a group and generated random number not to be same as Row_number. I cannot write Stored Procedure, it should be with SQL

Ex:

Row_number  Group     Desired O/pRandom_number  
1            1         3
2            1         4
3            1         2
4            1         1
1            2         2
2            2         1
3            3         4
4            3         5
5            3         3

I tried to calculate max and min within each group and generate the random numbers and everything looks fine. Issue is sometimes same random number will be assigned to row_number. I always want the random numbers and row_number to be always different

Upvotes: 0

Views: 158

Answers (2)

Popeye
Popeye

Reputation: 35910

you dont want to use row_number in your solution, try to use sum analytical function with random ordering of rows.

I am not sure if it is allowed in your case or not but you can give a try.

Sum(1) over (partition by group order by dbms_random.value())

Cheers!!

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270301

Just add 1 and use modulo arithmetic:

select t.*,
       mod(row_number + 1, count(*) over (partition by group)) + 1 as desired
from t;

Upvotes: 1

Related Questions