Oracle randomly shuffling entire rows

0
votes

have a locations table that contains 3 types of records (see below). I only need to SELECT record_type = 'G' rows.

I want to wrap the data in a loop ( ie 5 times) but how can I randomly shuffle the ENTIRE rows, by location_id, so they appear in a different order with each iteration of the loop? Perhaps, sticking the rows into an array and shuffling the array?

Note, I don't want to shuffle the columns, I want the row kept in tact. In the future more locations maybe added.

CREATE TABLE locations AS
SELECT level AS location_id,
       'Door ' || level AS location_name,

CASE round(dbms_random.value(1,3)) 
            WHEN 1 THEN 'A' 
            WHEN 2 THEN 'T' 
            WHEN 3 THEN 'G' 
         END AS location_type

FROM   dual
CONNECT BY level <= 25;

SELECT * from tour_detail 
Order by tour_id, tour_time

TOUR_ID TOUR_TIME    LOCATION_ID
1   06212020 00:10:25   2
1   06212020 00:21:05   18
1   06212020 00:30:33   11
1   06212020 00:40:51   17
1   06212020 00:52:13   4
1   06212020 01:01:42   2
1   06212020 01:07:52   11

We have an access control system when you slide your card it records card_num, location_id, access_date in MMDDYYY HH24:MI:SS format.

Some wanted to create a guard tour system. That compares the access_date to the tour_detail detail date. If within a certain criteria say + or - 2 minutes that means the guard was on time, if GT or LT 2 minutes we log an early or late, if GT Or LT 5 minutes or no access_history record we log a no show.

I was asked to throw together some testing data. As you can see the tour_detail records contain MMDDYYYY HH24:MI:SS so I'm thinking about changing the tour_detail records to interval hour to minute but I have little experience working with that.

2
sqlarraysoracleshuffle
"randomly shuffle" and "in a different order with each iteration of the loop" are not achievable at the same time. With a random shuffle, you can get the same order twice or more. Do you want to iterate through every possible order combination, once? Or do you just want a random ordering every time? - Lasse V. Karlsen
No I want to just shuffle it don't need every combination different. I want this How could do this: BEGIN FOR l_counter IN 1..5 LOOP For 1 to every location.count Loop Print location_id End loop Randomly shuffle all records in locations table END LOOP; END; - Beefstu

2 Answers

1
votes

so they appear in a different order with each iteration of the loop

This is pointless. You are inserting rows into a table. Rows (in a RDBMS table) aren't stored in any particular order.

But, you can sort them when selecting from that table and - in order to do that - you have to use the order by clause.

As of selecting, see if this helps:

SQL> with data as
  2    (select empno, ename, job, sal
  3     from emp
  4     where deptno = 20
  5     order by round(dbms_random.value(0, 15))
  6    )
  7  select column_value, d.*
  8  from data d cross join table(cast(multiset(select level from dual
  9                                             connect by level <= 5
 10                                            ) as sys.odcinumberlist));

COLUMN_VALUE      EMPNO ENAME      JOB              SAL
------------ ---------- ---------- --------- ----------
           4       7902 FORD       ANALYST         3000
           4       7788 SCOTT      ANALYST         3000
           2       7369 SMITH      CLERK           1000
           4       7369 SMITH      CLERK           1000
           1       7369 SMITH      CLERK           1000
           5       7566 JONES      MANAGER         2975
           1       7876 ADAMS      CLERK           1100
           1       7788 SCOTT      ANALYST         3000
           5       7788 SCOTT      ANALYST         3000
           3       7876 ADAMS      CLERK           1100
           1       7902 FORD       ANALYST         3000
           2       7876 ADAMS      CLERK           1100
           5       7902 FORD       ANALYST         3000
           3       7902 FORD       ANALYST         3000
           3       7566 JONES      MANAGER         2975
           1       7566 JONES      MANAGER         2975
           3       7788 SCOTT      ANALYST         3000
           2       7902 FORD       ANALYST         3000
           4       7566 JONES      MANAGER         2975
           5       7876 ADAMS      CLERK           1100
           2       7788 SCOTT      ANALYST         3000
           3       7369 SMITH      CLERK           1000
           4       7876 ADAMS      CLERK           1100
           2       7566 JONES      MANAGER         2975
           5       7369 SMITH      CLERK           1000

25 rows selected.

SQL>
0
votes

Let's say you have a table with 5 IDs

create table tab as
select rownum id from dual connect by level <= 5;

THis produce a random order output of the rows

select * from tab order by dbms_random.value;

        ID
----------
         2
         4
         5
         1
         3

If you repeat the statment you get a different (random) result

        ID
----------
         5
         4
         2
         1
         3

So simple repaet the query in the loop or make something like this

with data as (
select 1 pass_id, id  from tab  
union all
select 2 pass_id, id  from tab  
union all
select 3 pass_id, id  from tab )
select * from data
order by 1,dbms_random.value