Oracle sorting, oracle

Oracle sorting, oracle

1. Processing of NULL in ORDER

By default, Oracle considers null as the maximum value in Order by. Therefore, if it is ASC, it is placed at the end of the ascending Order, and DESC is placed at the top of the descending Order.

Of course, you can also use nulls first or nulls last syntax to control the NULL position. Nulls first and nulls last are syntaxes supported by Oracle Order by. If the expression Nulls first is specified in Order by, the records with null values are ranked first (whether asc or desc) if the expression Nulls last is specified in Order by, the records with null values (whether asc or desc) will end with the following syntax:

-- Always put nulls in the first select * from table name order by column name nulls first

-- Always put nulls in the last select * from table name order by column name last

 

2. Writing several sort statements in ascending order:

 

Select <column_name> from <table_name> order by <column_name>; (Ascending by default, even if ASC is not written)

Single Column descending order: select <column_name> from <table_name> order by <column_name> desc;

Multi-column Ascending order: select <column_one>, <column_two> from <table_name> order by <column_one>, <column_two>;

Multi-column descending order: select <column_one>, <column_two> from <table_name> order by <column_one> desc, <column_two> desc;

Multi-column hybrid sorting: select <column_one>, <column_two> from <table_name> order by <column_one> desc, <column_two> asc;

 

 

 

Irregular SQL sorting

 

select * from (  select 1 a,1 b from dual union all   select 1 a,2 b from dual   union all   select 10 a,2 b from dual    union all   select 100 a,2 b from dual  ) xxx order by decode(a,100,0,a)

Equivalent statement:

 

 select 100 a,2 b from dual  union all select * from (  select 1 a,1 b from dual union all   select 1 a,2 b from dual   union all   select 10 a,2 b from dual  ) xxx