|
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:
| CD Table |
| BAND_NAME |
ALBUM_NAME |
PUBLISHER |
COST |
| Big Bad Voodoo Daddy |
Big Bad Voodoo Daddy |
BMI |
12.99 |
| Levay Smith |
One Hour Mama |
Fat Note |
13.99 |
| Louis Prima |
Capitol Collector's Series |
Capitol |
9.99 |
| Indigo Swing |
Indigo Swing |
Wet and placket |
13.99 |
| Louis Jordan |
Rock and Roll |
Mercury |
12.99 |
| Louis Jordan |
Best Of |
Capitol |
10.99 |
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
-------------------------------
Previous |
Next |
Table of Contents
|
