Select Random Records in Oracle

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.