Cyber Drop

Anyone with proper understanding on ORDER BY clause will not be surprised by this post :) Still, I feel it’s worth sharing.

Query 1

SELECT employee_id , last_name,salary,department_id FROM employees ORDER BY 1;

This is positional sorting. So, the result set will be ordered by employee_id column in ascending order.

Query 2

SELECT employee_id , last_name,salary,department_id FROM employees ORDER BY 5;

It will throw an error “ORA-01785: ORDER BY item must be the number of a SELECT-list expression” as there is NO 5^th column listed in the SELECT list. (employee_id is the 1^st column listed in the SELECT statement, last_name is the second. salary is the third and department_id is the fourth. So, there is no 5^th column in SELECT list)

Query 3

SELECT employee_id , last_name,salary,department_id FROM employees ORDER BY 4+2-1;

4+2-1 is 5 so, should have the same behavior as Query 2? If you said, ‘yes’ then you need to rework on your basics J

This query 3 will run fine with out any error. As you can see here in the documentation Oracle Database SQL Language Reference, you can specify an expression or a position. The position is an integer. As in Query 1, if in case we have given "order by 1" then the 1 is treated as a position of the column in SELECT list whereas "4+2-1" is treated as an expression, which yields 5. And this becomes an ordering by a constant, in other words a non operation.