Thanks for the question, Satish.

Asked: August 06, 2009 - 4:59 pm UTC

Last updated: August 28, 2017 - 5:34 am UTC

Version: 10.2.0.3

Viewed 10K+ times! This question is

You Asked

Hi tom,

To display the random order of rows every time , I give

SQL> select object_id from user_objects order by dbms_random.value

It displays what I wanted.

but if I give

SQL> select object_id from user_objects order by .88439942 --(which is a value of dbms_random.value)

ERROR at line 1:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression

I can understand the above error but surprised on how 'Order by dbms_random.value' is working.

Can you make me understand?

Thanks
Satish

and Tom said...

when you "order by NUMERIC_LITERAL", the SQL standard says "that is an ordinal value that is a column position - like 1, 2, 3, ... N."

select ename, empno from emp order by 1;

that is just like "order by ename". It is not ordering by the constant 1, it is ordering by the first column in the output.

select ename, empno from emp order by 2;

that is just like "order by empno".....

when you order by <expression returning a number>, it orders by the expression, not a "column"

think about it, otherwise "order by empno" would not work, empno is just an expression as is dbms_random.value.

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, August 07, 2009 - 2:57 pm UTC

so what I unserstand from your above reply is it will fetch each and assign a random and then sorted on the assigned value

Please confirm

Thanks

August 07, 2009 - 3:45 pm UTC

select * from t order by dbms_random.value()

is a lot like

select * from t order by upper(ename);

or even dare I say

select * from t order by ename;

pretty much exactly like it - you are ordering by an expression. The result of that expression is what the data set is ordered by.

Satish Kandula, August 09, 2009 - 12:24 pm UTC

Thanks Tom!

TO_NUMBER

Karthick, August 10, 2009 - 6:55 am UTC

If we use 1,2... in the ORDER BY clause it will be substituted with the column name as said by tom so if you actually want to use a constant you can use TO_NUMBER function. So that the ORDER BY clause will evaluate it as an expression and order it by that value.

select object_id from user_objects order by TO_NUMBER(.88439942 )

But any way a constant value in a ORDER BY i dont find any use out of it.

How About This

Steve G, August 11, 2009 - 10:27 am UTC

Just add the dbms_random.value as a column to the query and order by that. Won't that give you your intended results.

select
table_name
, dbms_random.value as ran
from dba_tables
order by ran

A reader, August 28, 2017 - 2:13 am UTC

I think that
'select object_id from user_objects order by dbms_random.value '
dbms_random.value as a function be used by oracle compiler,so here just order by one column.

August 28, 2017 - 5:34 am UTC

Is there a question here ?

Breadcrumb

Question and Answer