ORDER BY的一些小知识点

文档地址：http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm#i2171079

 

order_by_clause::=

order_by_clause

Use the ORDER BY clause to order rowsreturned by the statement. Without anorder_by_clause, no guaranteeexists that the same query executed more than once will retrieverows in the same order.

SIBLINGS The SIBLINGSkeyword is valid only if you also specify thehierarchical_query_clause(CONNECTBY). ORDERSIBLINGS BY preserves any orderingspecified in the hierarchical query clause and then applies theorder_by_clause to the siblingsof the hierarchy.

expr expr orders rows based on theirvalue forexpr. The expression is based oncolumns in the select list or columns in the tables, views, ormaterialized views in theFROM clause.

position Specifyposition to order rows based ontheir value for the expression in this position of the select list.Theposition value must be aninteger.

 

You can specify multiple expressions in theorder_by_clause.

 

Oracle Database first sorts rowsbased on their values for the first expression. Rows with the samevalue for the first expression are then sorted based on theirvalues for the second expression, and so on.--oracle数据库在首次排序行是基于第一个表达式的那些值。然后在第一个表达式相同的值的行中再基于第二个表达式的值排序，以此类推。然后在第一个表达式和第二个表达式（共同组成的值）相同的行中，再基于第三个表达式的值进行排序--假如排序子句中(order_by_clause)有三个表达式。

 

The database sorts nulls following all others inascending order and preceding all others in descending order.--数据库排序当升序的时候，NULL在所有其他值后边，如果是降序NULL在所有其他值的前边。

 

 

Please refer to "Sorting Query Results" for a discussion of ordering queryresults.

ASC | DESC Specify whether theordering sequence is ascending or descending.ASC isthe default.

NULLS FIRST | NULLS LAST Specifywhether returned rows containing null values should appear first orlast in the ordering sequence.

NULLS LAST is the default forascending order, and NULLS FIRST is thedefault for descending order.

Restrictions on the ORDER BYClause The following restrictions apply totheORDER BY clause:

• If you have specified the DISTINCT operator in thisstatement, then this clause cannot refer to columns unless theyappear in the select list.

• An order_by_clause can contain nomore than 255 expressions.

• You cannot order by a LOB column, nested table, or varray.

• If you specify a group_by_clause in the same statement,then this order_by_clause is restricted tothe following expressions:

  • Constants

  • Aggregate functions

  • Analytic functions

  • The functions USER, UID, andSYSDATE

  • Expressions identical to those in the group_by_clause

  • Expressions comprising the preceding expressions that evaluateto the same value for all rows in a group