Chapter 8 Home | Prev: 8.6.2 Filtering with Aliases | Next: 8.8 Limiting Results and Paging

8.7 Sorting Data with ORDER BY

Image trying to find your friend in the Toronto (6.1 million people) phone book if it was sorted in the order in which the data was entered and not alphabetical.   It would take you forever to find the exact record you are looking for.  Therefore, it is very important that query results are sorted in a logical way such that it is easy to navigate the results. 

Oracle enforces a primary key integrity constraint by creating a unique index on the primary key. This index is automatically created by Oracle when the constraint is enabled; no action is required by the issuer of the CREATE TABLE or ALTER TABLE statement to create the index.

In these cases, output from an SQL command is automatically sorted by the primary key unless otherwise specified. In SQL, this is accomplished through the ORDER BY part of the SELECT statement.

SELECT firstname, lastname 
FROM employees
ORDER BY lastname;

sorts the data alphabetically by last name from A to Z.  This sorting can happen in both ascending ASC and descending DESC order: the default is ascending if not specified.

SELECT firstname, lastname 
FROM employees
ORDER BY lastname DESC;

sorts the data alphabetically by last name from Z to A.

There are three ways to sort things and it is important to know the differences:

alphabetically	sorts A to Z or Z to A
numerically	sorts -9 to 0 to 9
chronologically	sorts the earliest date to the latest date

Sorting Using More Than One Field

When you sort by more than one field, it is important to understand that the second field is only ever even considered when the first field has exact duplicates.

SELECT firstname, lastname
FROM employees
ORDER BY lastname, firstname;

sorts the entire list by lastname period. If and only if there are duplicates in the lastname field, then only the duplicates are sorted secondarily by firstname.

If sorting is performed with many fields indicated, the probability that the later ones will ever get used drastically decreases. Each level of sorting will only be performed if ALL levels before it exactly match. For example: if two people have the same lastname, same firstname, same birthdate, and live in the same city, then maybe postal code might be used to sort them.

Sorting and Aliases

When using aliases in the ORDER BY part of the statement, there are two consideration;

1. Order of Execution will have no affect on ORDER BY and aliases, as all aliases would have already been defined at the time ORDER BY executes.  Therefore, both table and field aliases are eligable for use in the ORDER BY portion.
    
2. Aliases are often used on calculated fields and this creates another gotcha you need to be aware of.  Some built-in, or single-row, functions actualy change the data type of the output.  This means that the sorting may not occur as expected as there may be different algorithms for alphabetical, numeric and chronological sorting.

    SELECT 
        ordernumber, 
        FORMAT(orderdate, 'mmm dd, yyyy') AS dtOrder, 
        status
    FROM orders
    WHERE extract(year FROM orderdate) = 2004
    ORDER BY dtOrder;

This query will output all the orders in 2004, but the order will be incorrect. The FORMAT() function converts the orderdate from a data type to a string type. This results in the sorting being alphabetical rather than chronological: meaning all April orders are shown first, followed by August etc. Obviously we want to show January orders first, followed by February. This is achieved by using the original field in the ORDER BY part to keep the data type as a date type and maintaining the chronological ordering.