user6185827 user6185827 - 6 months ago 12
SQL Question

use random function in SQL for one particular column only

I have two questions:


  1. Is there any possible way to use
    dbms_random.random
    OR
    dbms_random.value
    in Oracle by specifying for one column only?

  2. If the above is possible, can i limit the randomly generated value of occurrences maximum 5 times, for example?



I am using Oracle 11g :) Thank you.

free
Table:

freeID | lecturerID | availableID
1 1004 10
2 1004 11
3 1004 12
4 1004 13
5 1005 11
6 1005 12
7 1005 13
8 1005 14


This is my query:

SELECT DISTINCT e1.FreeID, e1.lecturerID, e1.AVAILABLEID,
e2.FREEID, e2.LECTURERID, e2.AVAILABLEID,
s.studentID, s.studentName, s.lecturerID
FROM free e1
INNER JOIN free e2
ON e1.availableID = e2.availableID
JOIN student s
ON s.lecturerID = e1.LECTURERID
AND e1.lecturerID='1004'
AND e2.lecturerID='1005'
AND e1.AVAILABLEID = e2.availableid


Based on above query, I
INNER JOIN
free
table with
availableID
to look for
lecturerID
with the same
availableID
.

Then, I
JOIN
again with
student
table with
lecturerID


Expected outcome:

freeID | e1.lecturerID | e1.availableID | e1.availableID | e2.lecturerID
1 1004 13 13 1005
2 1004 11 11 1005
3 1004 12 12 1005
4 1004 13 13 1005


I want the
availableID
column to randomly chose among them as long as they both have same value, each lecturer has the same
availableID
and does not exceed 5 time occurrences. If it exceeds, it will move to the next value.

Answer

You could use a window function to assign a row number, in random order, to each join within the same lecturerID, and then add the condition that only the 5 first should be taken:

SELECT *
FROM   (
        SELECT      e1.FreeID,
                    e1.lecturerID,
                    e1.availableID,
                    e2.lecturerID AS lectureID2,
                    s.studentID,
                    s.studentName,
                    ROW_NUMBER() OVER (PARTITION BY e1.lecturerID 
                                       ORDER BY dbms_random.random) AS rn
        FROM        free e1 
        INNER JOIN  free e2 
                ON  e1.availableID = e2.availableID
        INNER JOIN  student s
                ON  s.lecturerID = e1.lecturerID
        WHERE       e1.lecturerID = '1004'
               AND  e2.lecturerID = '1005'
        )
WHERE rn <= 5
Comments