At my work I am responsible for generating data for 90% of our contests. Once in a while there are requests where they want to pick out. i.e. ten random people. This quick tip is showing you one way how to pick random customers in Oracle using SQL.
In an earlier post (How To Get Random Dates In Oracle PLSQL), I already used the Oracle built-in function we are going to use, but this time we are going to use it in a SQL statement.
In this short post we are going to look at another sample on how to use the built-in Oracle dbms_random package. This package can be used in many ways, but for the purpose of this post we are just going to use the “value” function in it’s most plain way.
The use of the Oracle built-in dbms_random function
Let us say that we are working for a multi level marketing company, and that we are running a contest which is running over several weeks. The ones responsible for the contest wants to pick ten random people (out of many hundred) each week during the contest, and give them a prize. Or job is to come up with a smart way to do this. It is actually very simple:
FROM contest_result cr
WHERE contest_id = 1
AND cr.property_id = 24
ORDER BY dbms_random.value
WHERE ROWNUM <= 10
What you do is to order your contest table using “ORDER BY dbms_random.value“. Then you use “ROWNUM” to say that you i.e. want to have the ten first people in the list.
And, every time you run the query, the result should be different.
That is it, pretty simple, huh?
Hope this can help you in your busy workday.