ORDER BY no order

If you want your result set ordered by something, you better use the ORDER BY clause. If you don’t, there is no guarantee that the rows from the query will be ordered in any way. Fine, you may already know that. But, what about if you want to make sure that the rows from a query are returned in no specific order, i.e. in a random order. Here is how you can do it:

First, let’s query all_objects ordering by object_id:

scott@eddev> select ao.object_id, ao.object_name
  2  from all_objects ao
  3  where ao.object_type = 'FUNCTION'
  4  and rownum <= 8
  5  order by ao.object_id;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
      4219 DICTIONARY_OBJ_OWNER
      4221 DICTIONARY_OBJ_NAME
      4223 DATABASE_NAME
      4233 DES_ENCRYPTED_PASSWORD
      4247 DICTIONARY_OBJ_OWNER_LIST
      4249 DICTIONARY_OBJ_NAME_LIST
      4253 CLIENT_IP_ADDRESS
     22032 DBJ_SHORT_NAME

8 rows selected.

Now, simply order by dbms_random.random:

scott@eddev> select ao.object_id, ao.object_name
  2  from all_objects ao
  3  where ao.object_type = 'FUNCTION'
  4  and rownum <= 8
  5  order by dbms_random.random;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
      4247 DICTIONARY_OBJ_OWNER_LIST
      4249 DICTIONARY_OBJ_NAME_LIST
      4221 DICTIONARY_OBJ_NAME
     22032 DBJ_SHORT_NAME
      4253 CLIENT_IP_ADDRESS
      4219 DICTIONARY_OBJ_OWNER
      4223 DATABASE_NAME
      4233 DES_ENCRYPTED_PASSWORD

8 rows selected.

There you go, the rows are guaranteed to have no specific order. Let’s run the same query again:

scott@eddev> select ao.object_id, ao.object_name
  2  from all_objects ao
  3  where ao.object_type = 'FUNCTION'
  4  and rownum <= 8
  5  order by dbms_random.random;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
      4221 DICTIONARY_OBJ_NAME
      4253 CLIENT_IP_ADDRESS
     22032 DBJ_SHORT_NAME
      4249 DICTIONARY_OBJ_NAME_LIST
      4233 DES_ENCRYPTED_PASSWORD
      4223 DATABASE_NAME
      4219 DICTIONARY_OBJ_OWNER
      4247 DICTIONARY_OBJ_OWNER_LIST

8 rows selected.

See, they are not in the same order.

Well, why would I want to return rows in a random order? you may ask. A couple of scenarios that come to mind are to select random quotes, and maybe a random ad banner to display on your site.

Finally, Tom Kyte wrote about the ORDER BY on his blog, and discussed randomizing the order of rows on AskTom.

Related articles:

Tagged , , | Post a Comment