René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

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 position

order 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 nth 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 2-1;

       NUM TXT
---------- ----------
         3 three
         4 four
         2 two
         1 one
         5 five

Column aliases

create 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'));

select
  id,
  to_char(dt, 'dd.mm.yyyy') dt
from
  order_date
order by
  dt;
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

Nulls

order 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.