In this article, we will learn what a developer should know about Order by clause in Oracle.

• When multiple columns/expressions are specified in the ORDER BY clause, the precedence of sorting is left to right.
• The ORDER BY clause can order in ascending (ASC) or descending (DESC) sequence, or a mix of both. If ASC or DESC is not explicitly stated, then ASC is the default.
• ORDER BY ASC places NULL values at the end of the query results. ORDER BY DESC places null values at the start of the query results.
• The default placement of NULLs with ORDER BY can be changed with the addition of NULLS FIRST/NULLS LAST to the ORDER BY clause.
• It is a myth that DISTINCT and GROUP BY will sort data so ORDER BY need not be specified. Without an ORDER BY clause, there is no guarantee that the same query executed again will retrieve rows in the same order. Do not rely on it even if Oracle appears to sort the data. If you want order, use ORDER BY.
• If your query is a hierarchical query, do not use ORDER BY as that will destroy the hierarchical order of the CONNECT BY results. To order rows under the same parent, use the ORDER SIBLINGS BY clause.
• Instead of column names in the SELECT list, column positions or aliases can be specified to order rows. The position value must be an integer.
• In compound queries containing set operators UNION, INTERSECT, MINUS, or UNION ALL, must specify positions or aliases rather than explicit expressions and must appear only in the last component query. Also, the ORDER BY clause sorts the entire set of rows returned by the compound query.
• If the DISTINCT operator appears in the SELECT statement, then ORDER BY cannot refer to columns unless they appear in the SELECT list. ORDER BY in a query without the DISTINCT operator can refer to columns outside the SELECT list.
• The ORDER BY clause can contain a maximum of 255 columns/expressions.
• You cannot order by an LOB column, nested table, or varray.
• You can use expressions in the ORDER BY clause for complex sorting requirements.
• ROWNUM is assigned to rows as they satisfy the predicate before the ORDER BY clause is applied. So ROWNUM values do not necessarily come out sequentially in a query with ORDER BY clause.
• To assign ROWNUM values after rows get sorted by ORDER BY, use an inline view
• To display rows in random order every time a SQL is executed, use order by dbms_random.value.

© 2015, https:. All rights reserved. On republishing this post, you must provide link to original post