Oracle ORDER BY Statement usage detailed

When you perform a query operation, the row data is displayed by default in the order in which the row data is inserted, but in practice it is often necessary to sort the data to show more intuitive data, with the ORDER BY clause being used to sort the data. The syntax is as follows:

SELECT <*,column [alias],... > from table [WHERE Condition[s]] [ORDER by] expr [asc| DESC];

1. Ascending sort

By default, when you perform a sort operation with order by, the data is sorted in ascending order, or the ASC keyword is specified after the sequence is sorted. Note: When an ascending sort is performed, if the sorted sequence contains null values, then NULL is displayed at the last side. Such as:

Sql> SELECT ename,sal from the EMP WHERE deptno=30 ORDER by Sal;

2. Descending sort

In order to perform a descending sort, you must specify the DESC keyword. Note: When a descending sort is performed, NULL is displayed at the top if there is a null value in the row list. Such as:

Sql> SELECT ename,sal from the EMP WHERE deptno=30 order by Sal DESC;

3. Using multiple-column sorting

When you sort by using the ORDER BY clause, not only can you sort on a single column or a single expression, or you can base on multiple columns or multiple expressions, and when you sort with multiple columns or multiple expressions, you first follow the first column or expression, and when the first column or expression has the same data, Then sort by the second column or expression. Such as:

Sql> SELECT Ename,sal,comm from the EMP WHERE deptno=30 ORDER by Sal Asc,comm DESC;

4. Sorting using an optional column

When you perform a sort operation by using the ORDER BY clause, in most cases the selection list contains the sorted sequence, but in practice, the selection list may not contain a row sequence. Such as:

Sql> SELECT Ename,job from the emp order by Sal DESC;

5. Use column alias sort

If you have an alias defined for a column or expression, you can sort by using a column or an expression when you perform a sort operation, or by using a column alias. Such as:

Sql> SELECT ename,sal*12 as "annual wage" from EMP WHERE deptno=30 ORDER BY "annual wage" DESC;

6. Sorting using column position numbering

When you perform a sort operation, you can specify not only the column name, the column alias, but also the position of the column or expression in the selection list. If the column name or expression name is very long, using column position sorting can shorten the length of the sort statement, and when merging query results by using collection operations such as Union,nnion All,intersect,minus, the column position must be used if the list has different column names and you want to sort. Such as:

Sql> SELECT ename,sal*12 from the EMP WHERE deptno=20 order by 2 DESC;