Order By

So far we have focussed on simply grabbing data from our database. We have not spent much time talking about how we can massage that data. One of the most common ways to manipulate data grabbed from a database is to order it, perhaps alphabetically by last name, or perhaps numerically, from high to low.

SQL provides the ORDER BY operator for just this purpose. The generic use of ORDER BY looks something like:

    SELECT column_names 
    FROM table
    WHERE where_clause [OPTIONAL]
    ORDER BY column_name;

Let's look at this by example. Suppose we have the following table:

Thus, if you would like to get an alphabetized list of artists, you would use:

    SELECT BAND_NAME, COST
    FROM CD
    ORDER BY BAND_NAME;

You should get the following:

    BAND_NAME			COST
    -------------------------------
    Big Bad Voodoo Daddy	12.99
    Indigo Swing		13.99
    Levay Smith			13.99
    Louis Jordan		12.99
    Louis Jordan		10.99	
    Louis Prima		 	 9.99
    -------------------------------

Note that you can also specify the column number instead of the column name when defining an ORDER BY so that the following command would be just the same as the previous:

    SELECT BAND_NAME, COST
    FROM CD
    ORDER BY 1;

Of course, you can also sort by multiple columns. That is, you specify an initial column to order by and then you choose subsequent columns with which to sort identical values in the initial column. For example, notice that if we sort by COST as well as BAND_NAME, the albums for Louis Jordan are sorted by cost as well as name:

    SELECT BAND_NAME, COST
    FROM CD
    WHERE BAND_NAME LIKE 'Louis%'
    ORDER BY BAND_NAME, COST;

You should get the following in which the 10.99 album is now listed first:

    BAND_NAME			COST
    -------------------------------
    Louis Jordan		10.99
    Louis Jordan		12.99	
    Louis Prima			 9.99
    -------------------------------

Finally, you can use the DESC keyword to reverse the sort. Thus, to get a report sorted by cost in a descending order, you would use:

    SELECT BAND_NAME, COST
    FROM CD
    ORDER BY COST DESC;

You would get the following view:

    BAND_NAME			COST
    -------------------------------
    Levay Smith			13.99
    Indigo Swing		13.99
    Big Bad Voodoo Daddy	12.99
    Louis Jordan		12.99
    Louis Jordan		10.99
    Louis Prima			 9.99
    -------------------------------

Null
Introduction to Databases for the Web | Table of Contents
Performing Math