|
So far, we have seen how to get
data out of a table using SELECT and WHERE commands.
Often, however, we need to list
the output in a particular order.
This could be in ascending order,
in descending order, or could be
based on either numerical value or
text value. In such cases, we can
use the ORDER BY keyword to
achieve our goal. The syntax for
an ORDER
BY statement
is as follows:
SELECT "column_name"
FROM "table_name"
[WHERE "condition"]
ORDER BY "column_name"
[ASC, DESC]
The [] means that the WHERE statement
is optional. However, if a WHERE
clause exists, it comes before the ORDER
BY clause. ASC means
that the results will be shown in
ascending order, and DESC means
that the results will be shown in
descending order. If neither is
specified, the default is ASC.
It is possible to order by more
than one column. In this case, the ORDER
BY clause
above becomes
ORDER
BY "column_name1"
[ASC, DESC], "column_name2"
[ASC, DESC]
Assuming that we choose
ascending order for both columns,
the output will be ordered in
ascending order according to
column 1. If there is a tie for
the value of column 1, we the sort
in ascending order by column 2.
For example, we may wish to
list the contents of Table
Store_Information by dollar
amount, in descending order:
Table Store_Information
| store_name |
Sales |
Date |
| Los Angeles |
$1500 |
Jan-05-1999 |
| San Diego |
$250 |
Jan-07-1999 |
| San Francisco |
$300 |
Jan-08-1999 |
| Boston |
$700 |
Jan-08-1999 |
SELECT
store_name, Sales, Date
FROM Store_Information
ORDER BY Sales DESC
Result:
| store_name |
Sales |
Date |
| Los Angeles |
$1500 |
Jan-05-1999 |
| Boston |
$700 |
Jan-08-1999 |
| San Francisco |
$300 |
Jan-08-1999 |
| San Diego |
$250 |
Jan-07-1999 |
In addition to column name, we
may also use column position
(based on the SQL query) to
indicate which column we want to
apply the ORDER
BYclause. The first column
is 1, second column is 2, and so
on. In the above example, we will
achieve the same results by the
following command:
SELECT
store_name, Sales, Date
FROM Store_Information
ORDER BY 2 DESC
| 
