Sorting Rows Using the ORDER BY Clause

You use the ORDER BY clause to sort the rows retrieved from the database. The ORDER BY clause may specify one or more columns on which to sort the data and must follow the FROM clause or the WHERE clause (if a WHERE clause is supplied).

The following example uses the ORDER BY clause to sort the last_name column values from the customers table:

  SELECT *   FROM customers   ORDER BY last_name;  CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE ----------- ---------- ---------- --------- ------------  4 Gail Black 800-555-1214  5 Doreen Blue 20-MAY-70  1 John Brown 01-JAN-65 800-555-1211  2 Cynthia Green 05-FEB-68 800-555-1212  3 Steve White 16-MAR-71 800-555-1213 

By default, the ORDER BY clause sorts the columns in ascending order (lower values appear first). You can use the DESC keyword to sort the columns in descending order (higher values appear first). You can also use the ASC keyword to explicitly specify an ascending sort ”as I mentioned, this is the default, but you can still specify it.

The following example uses the ORDER BY clause to sort the first_name column values from the customers table in ascending order, followed by a sort on the last_name column values in descending order:

  SELECT *   FROM customers   ORDER BY first_name ASC, last_name DESC;  CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE ----------- ---------- ---------- --------- ------------  2 Cynthia Green 05-FEB-68 800-555-1212  5 Doreen Blue 20-MAY-70  4 Gail Black 800-555-1214  1 John Brown 01-JAN-65 800-555-1211  3 Steve White 16-MAR-71 800-555-1213 

You can also use a column position number in the ORDER BY clause to indicate which column to sort: 1 means sort by the first column selected, 2 means sort by the second column, and so on. In the following example, column 1 (the customer_id column) is used to sort the rows:

  SELECT customer_id, first_name, last_name   FROM customers   ORDER BY 1;  CUSTOMER_ID FIRST_NAME LAST_NAME ----------- ---------- ----------  1 John Brown  2 Cynthia Green  3 Steve White  4 Gail Black  5 Doreen Blue 

Because the customer_id column is in position 1, it is the column used in the sort.