Recently, I needed to sort the result of a query in random and show a limited number of records. Honestly, I didn’t know how to do it and had to search various online forums to get the correct answer. So for my own sanity, I’ll be posting the solution here.

Step 1
SELECT field_name1, field_name2
FROM table_name
WHERE field_query1 = 'ARGUMENT VAL'
ORDER BY dbms_random.VALUE;

The key here is the function dmbs_random.VALUE that generates, you guess it, random number. Its value is then used for ordering. The second step is to limit the output by using ROWNUM as below.

Step 2
SELECT field_name1, field_name2
FROM (SELECT field_name1, field_name2
FROM table_name
WHERE field_query1 = 'ARGUMENT VAL'
ORDER BY dbms_random.VALUE)
WHERE ROWNUM < 11;

If you’ll notice, I used a subquery here. Why didn’t I just include the rownum inside the subquery? It is because I wanted the subquery to finish the random ordering first before limiting the output to 10.

Real World Example

SELECT order_num, pr_postn_id
FROM (SELECT order_num, pr_postn_id
FROM siebel.s_order
WHERE order_num LIKE 'V-%'
ORDER BY dbms_random.VALUE)
WHERE ROWNUM < 11;

I'll also need this similar solution for MySQL and will be discussed in my next post.

4 Thoughts on “Select Random Records in Oracle

  1. Reynold P J on April 28, 2009 at 6:50 am said:

    Thanks a lot ………..

  2. Brilliant.!

  3. TheNeels on November 15, 2011 at 12:56 am said:

    HI. Thanks for this. Works like a charm. I come from a MSSQL environment, and being used to just popping this type of statement in a function/Stored Proc would have been quick. It looks like you have to jump through hoops in Oracle to get this type of statement in a function?

    I found this: hxxp://www.java2s.com/Tutorial/Oracle/0540__Function-Procedure-Packages/Returnatablecollection.htm

    Do you really have to go through all of this effort or is there an easier way?

  4. Thanks a lot ….

Post Navigation