EnterpriseDB: Ordering Rows Oracle Tips by Burleson Consulting

When selecting all of the rows from emp, the rows come out in a random order (theoretically at least).  They may have come out in the order they were entered.  They may even have come out alphabetically if they were entered that way.

The SQL standard, however, says that SQL results are in an un-ordered order unless you use the ORDER BY keywords.  The ORDER BY keywords tells the SQL engine the exact order that you would like to see the data.

To continue our earlier example, we can choose to view the emp records ordered by ename:

SELECT empno, ename
  FROM emp
  ORDER BY ename;

empno | ename
-------+--------
  7876 | ADAMS
  7499 | ALLEN
  7698 | BLAKE
  7782 | CLARK
  7902 | FORD
  7900 | JAMES
  7566 | JONES
  7839 | KING
  8000 | LEWIS
  7654 | MARTIN
  7934 | MILLER
  7788 | SCOTT
  7369 | SMITH
  7844 | TURNER
  7521 | WARD

(15 rows)

You can have multiple columns in the ORDER BY just like in the SELECT list.  As a matter of a fact, you can have columns in the ORDER BY clause that are not in the select list. 

SELECT empno
  FROM emp
  ORDER BY ename;

empno
-------
  7876
  7499
  7698
  7782
  7902
  7900
  7566
  7839
  8000
  7654
  7934
  7788
  7369
  7844
  7521

(15 rows)

In this command I selected the empno column for all rows, ordered by ename.  It doesn't make much sense to me to order by columns not included in the select list, but the functionality is there should you need it.

SELECT empno, ename
  FROM emp
  ORDER BY ename, empno;

empno | ename
-------+--------
  7876 | ADAMS
  7499 | ALLEN
  7698 | BLAKE
  7782 | CLARK
  7902 | FORD
  7900 | JAMES
  7566 | JONES
  7839 | KING
  8000 | LEWIS
  7654 | MARTIN
  7934 | MILLER
  7788 | SCOTT
  7369 | SMITH
  7844 | TURNER
  7521 | WARD

(15 rows)

In the command above, I select empno and ename and ordered the result set by ename and then empno. 

There is an older form of ORDER BY syntax.  You can order by the numeric order of column in your select list.  For example, in the query above where I selected empno and ename from emp, I can order by ename and empno by using their positions within the select list.  The following command is functionally the same as the above command:

SELECT empno, ename
  FROM emp
  ORDER BY 2, 1;

It is considered poor practice to use the numeric version of the order by syntax.  It makes maintenance much more difficult later.  I wanted to mention it because it is highly likely you will see that syntax at some point.

By default, the rows returned using an ORDER BY are returned in ascending order.  You can force ascending order by using the keyword ASC.  If you want to reverse that, you can do that by using the DESC keyword.  If you do not include either ASC or DESC, ASC is assumed.

SELECT empno, ename
  FROM emp
  ORDER BY ename DESC, empno DESC;

empno | ename
-------+--------
  7521 | WARD
  7844 | TURNER
  7369 | SMITH
  7788 | SCOTT
  7934 | MILLER
  7654 | LEWIS
  7839 | KING
  7566 | JONES
  7900 | JAMES
  7902 | FORD
  7782 | CLARK
  7698 | BLAKE
  7499 | ALLEN
  7876 | ADAMS

(15 rows)

You can mix and match ASC and DESC as you like:

SELECT empno, ename
  FROM emp
  ORDER BY ename ASC, empno DESC;

This command would sort ename first in ascending order.  Any duplicate names would then be sorted in descending order by empno.

If a column in the ORDER BY clause is null, it will be sorted to the beginning of the list using ASC and to the bottom of the list using DESC.

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.