oracle order by 用法

Restrictions on the ORDER BY Clause The following restrictions apply to the ORDER BY clause:

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

• An order_by_clause can contain no more 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 to the following expressions:

  • Constants

  • Aggregate functions

  • Analytic functions

  • The functions USER, UID, and SYSDATE

  • Expressions identical to those in the group_by_clause

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

If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, then you can force the ROWNUM condition to be applied after the ordering of the rows. For example, the following query returns the employees with the 10 smallest employee numbers. This is sometimes referred to as top-N reporting:

SELECT * FROM
   (SELECT * FROM employees ORDER BY employee_id)
   WHERE ROWNUM < 11;