use random function in SQL for one particular column only

Question

I have two questions:

Is there any possible way to use dbms_random.random OR dbms_random.value in Oracle by specifying for one column only?
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.

What do you mean by one column only? Can you substantiate with an example schema and values? — Spade
– Spade, Commented May 6, 2016 at 16:32
It's a random number generator. They both generate a single random value. I'm confused by what you mean "for one column only" and "maximum 5 times". You would generate 5 random numbers if you wanted 5 numbers. — Dresden
– Dresden, Commented May 6, 2016 at 16:34
Those functions return individual values, They don't generate rows of data, they get standalone numbers or strings. Not sure what you've been reading but it must have been doing something more complicated. You can use the output of either function to set a single column value. You'll need to explain more about what you're trying to update and how - if you already have an update statement you want to modify, for example. — Alex Poole
– Alex Poole, Commented May 6, 2016 at 16:42
You're talking about generating random values and then you're not utilizing random numbers anywhere in the following example data and query. Does this question actually relate to random value generation or are you just looking to produce the outcome at the bottom? — Dresden
– Dresden, Commented May 6, 2016 at 16:55
Your data seems to be inconsistent - why are all the free IDs the same? should the 1002 lecturer IDs be 1005? - and you haven't shown the student table or what effect that has on the expected result. What are actually trying to achieve, in plain English not SQL terms? — Alex Poole
– Alex Poole, Commented May 6, 2016 at 16:57

trincot · Accepted Answer · 2016-05-06 17:43:06Z

2

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

edited May 6, 2016 at 17:43

answered May 6, 2016 at 17:21

trincot

357k38 gold badges282 silver badges338 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

user6185827 Over a year ago

Thank you for taking your time :). I tried but it says FROM keyword not found where expected

Thorsten Kettner Over a year ago

Because there is a comma missing after AS lectureID2.

user6185827 Over a year ago

Thank you @trincot . It is working well so far. I am still studying the query. Thank you again :)

trincot Over a year ago

Thanks, @ThorstenKettner, for mentioning the missing comma.

user6185827 Over a year ago

@trincot I have a question, does this query works well in large database?

|

Collectives™ on Stack Overflow

use random function in SQL for one particular column only

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related