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.