René Nyffenegger's collection of things on the web  
René Nyffenegger on Oracle  Most wanted  Feedback
 Follow @renenyffenegger

SQL: order by  
Yet to be finished....
The order by clause is used to return the result set of a select statement in a
desired order. Without such an order by clause, the result comes (in most cases) in no guaranteed order.
Order by positionorder by n
n must be an integer whose value is between 1 and the numbers of selected columns. Oracle will then order the result set by the
n^{th} position.
create table positional_order ( num number, txt varchar2(10) ); insert into positional_order values (3, 'three' ); insert into positional_order values (4, 'four' ); insert into positional_order values (2, 'two' ); insert into positional_order values (1, 'one' ); insert into positional_order values (5, 'five' ); select num, txt from positional_order order by 1; NUM TXT   1 one 2 two 3 three 4 four 5 five
If n is a computed expression, it behaves differently:
select num, txt from positional_order order by 21; NUM TXT   3 three 4 four 2 two 1 one 5 five Column aliasescreate table order_date ( id number, dt date ); insert into order_date values (1, to_date('26.02.1974', 'dd.mm.yyyy')); insert into order_date values (1, to_date('27.07.1982', 'dd.mm.yyyy')); insert into order_date values (1, to_date('28.08.1970', 'dd.mm.yyyy'));
This statement returns what probably was not intended, it returnes the result set ordered by the representation of the date rather
than the date itself:
ID DT   1 26.02.1974 1 27.07.1982 1 28.08.1970 select id, to_char(dt, 'dd.mm.yyyy') dt from order_date order by order_date.dt;
This statement returns what the result set really ordered by dt:
ID DT   1 28.08.1970 1 26.02.1974 1 27.07.1982 Nullsorder by expr nulls first order by expr nulls last
By default, an expression being null is returned after all non null expressions when
select in ascending order and before all non null expressions when selected in descending orders.
This behaviour can be changed by stating
order by expr nulls last . If someone wants to explicitely state (the default behaviour),
it's also possible to do order by expr nulls first :
create table order_by_with_nulls ( n number, v varchar2(10) ); insert into order_by_with_nulls values ( 5, 'five'); insert into order_by_with_nulls values ( 2, 'two'); insert into order_by_with_nulls values ( 8, 'eight'); insert into order_by_with_nulls values ( null, 'null'); insert into order_by_with_nulls values ( 4, 'four'); select * from order_by_with_nulls order by n; N V   2 two 4 four 5 five 8 eight null select * from order_by_with_nulls order by n nulls first; N V   null 2 two 4 four 5 five 8 eight Analytical functions
analytical functions are processed before the order by, therefore, analyitical functions are allowed as an expression in the order by.
See also order of select processing.
Links
Here is an example for order by that shows how to sort a book index.
