user6185827 - 1 year ago 49

SQL Question

I have two questions:

- Is there any possible way to use OR
`dbms_random.random`

in Oracle by specifying for one column only?`dbms_random.value`

- 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`

`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`

`availableID`

`lecturerID`

`availableID`

Then, I

`JOIN`

`student`

`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`

`availableID`

Answer Source

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
```