15 Things You Should Know about the ORDER BY Clause

1. When multiple columns/expressions are specified in the ORDER BY clause, the precedence of sorting is left to right.

2. 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.

3. 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.

4. The default placement of NULLs with ORDER BY can be changed with the addition of NULLS FIRST/NULLS LAST to the ORDER BY clause.

5. 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.

6. 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.

7. 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.

select employee_id, email
  from employees
    where department_id = 100
    order by email i.e. position #2
    order by 2;

EMPLOYEE_ID EMAIL

---

    109 DFAVIET
    111 ISCIARRA
    110 JCHEN
    112 JMURMAN
    113 LPOPP
    108 NGREENBE

6 rows selected.

1. 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.

2. 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.

   without DISTINCT

select manager_id
    from employees
    where rownum < 10
    order by department_id;

MANAGER_ID

   103
   103
   103
   103
   102
   100
   100
   101

9 rows selected.

with DISTINCT

select distinct manager_id
    from employees
    where rownum < 10
    order by department_id;
order by department_id

ERROR at line 4:
ORA-01791: not a SELECTed expression

1. The ORDER BY clause can contain a maximum of 255 columns/expressions.

2. You cannot order by an LOB column, nested table, or varray.

   create type varray_test
    as varray(5) of varchar2(30)
    /

Type created.

create table tbl_vt
    (
      id number,
     vcol  varray_test
    )
    /

Table created.

select id
    from tbl_vt
    order by vcol
    /
order by vcol

ERROR at line 3:
ORA-00932: inconsistent datatypes: expected - got HR.VARRAY_TEST

1. You can use expressions in the ORDER BY clause for complex sorting requirements.

e.g. In the table temp_tbl shown below, lets say we want values of condcol = 2 to appear first, then condcol = 1 and then condcol = 3. We can achieve this with a DECODE expression.

select *
    from temp_tbl;

C VALUE

---

1 1
1 2
2 3
2 4
3 5
3 6

 select *
    from temp_tbl
    order by decode(condcol,'2',1, 2)
           , condcol;

C VALUE

---

2          3
2          4
1          2
1          1
3          6
3          5

6 rows selected.

1. 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.

 select rownum, first_name
    from employees
    where rownum < 5
    order by first_name;

ROWNUM FIRST_NAME

---

     4 David
     1 Ellen
     3 Mozhe
     2 Sundar

1. To assign ROWNUM values after rows get sorted by ORDER BY, use an inline view:

sql
select rownum, t.*
from
(
select first_name
from employees
where rownum < 5
order by first_name
) t;


ROWNUM FIRST_NAME

---

     1 David
     2 Ellen
     3 Mozhe
     4 Sundar

1. To display rows in random order every time a SQL is executed, use order by dbms_random.value.

转载地址

http://www.oratable.com/oracle-order-by-clause/
如有侵权,立即删除