Introduction

Sometimes, The developer gets the requirement to produce a list of object or data in Random order. The Random order means that the developer should not control the order by using any other filtering criteria. The result should be purely Random and give a different or same set of records in a different position on multiple executions.
We can achieve the above problem by using Java as well as SQL query. Here, We will explain multiple ways to get the Random result by using the SQL query. Please find all possible ways to get the Random result. We used Oracle database to develop examples which are mentioned here.

---

SQL Query Examples for Oracle Database

---

SELECT * FROM SAMPLE_TABLE ORDER BY DBMS_RANDOM.RANDOM;

SELECT * FROM SAMPLE_TABLE ORDER BY DBMS_RANDOM.VALUE;

The above SQL query produces a result in Random order. The user can execute the above SQL query multiple time and will experience the records position will be different from the previous result. The DBMS_RANDOM package provides a built-in random number generator. DBMS_RANDOM is not intended for cryptography. It is having two operation –

---

• DBMS_RANDOM.RANDOM: It produces integers in [-2^^31, 2^^31).
• DBMS_RANDOM .VALUE: It produces numbers in [0,1) with 38 digits of precision.

---

select r
  from (select r
           from (select rownum r
                   from TABLE_NAME
                  where rownum < 50)
          order by dbms_random.value)
  where rownum <= 5;

The above query works by generating the numbers 1 .. 49, using the inline view. We wrap that innermost query as an inline view and sort it by a random value, using DBMS_RANDOM.VALUE. We wrap that result set in yet another inline view and just take the first six rows. If we run that query over and over, we’ll get a different set of six rows each time.

Result

    10
     2
    19
    34        
    21

---

SELECT *
FROM   (
    SELECT *
    FROM   table
    ORDER BY DBMS_RANDOM.VALUE)
WHERE  rownum < 21;

Select *
  from (select *
          from table
         order by DBMS_RANDOM.RANDOM)
 where rownum < 21;

The above SQL Query helps to produce 20 records randomly.

---

For Reference, Please refer below link.

---

https://www.databasejournal.com/features/oracle/article.php/3341051/Generating-random-numbers-and-strings-in-Oracle.htm

---

---