Sorting Your Results

The output from the above queries may not be in the desired order. For example, you may want the list of departments arranged alphabetically. Sorting is the process of rearranging data into some specific order. To sort the output into a desired sequence, a field or fields are specified that determine the order in which the results are arranged. These fields are called sort keys.

For example, if the department data is sorted into alphabetical order by department, the department name is the sort key. The budget field is the sort key if the department table is sorted by amount of budget. Note that the sort key can be numeric (budget) or character (department name).

Results can be sorted into ascending or descending sequence by sort key. Ascending means increasing order, and descending means decreasing order. For example, sorting the department table in ascending order by budget means the department data will be arranged so that the department with the lowest budget is first and the department with the highest budget is last. If we instead sorted in descending order, the department with the highest budget would appear first, the department with the lowest budget would appear last.

Sorting character data in ascending or descending order is based on a coding, or collating, sequence assigned to numbers and letters by the computer. For example, when department name is the sort key and you want the data arranged alphabetically, that indicates ascending order. If you want the data arranged in reverse alphabetical order, then specify descending order.

To sort your results using Texis, add the ORDER BY clause to the SELECT statement. The form of this clause is:

ORDER BY  column-name  [DESC]

where DESC indicates the rows are to be arranged in descending order. If DESC is omitted, your output is sorted in ascending order.

This clause fits into the SELECT expression following the WHERE clause, as shown below:

SELECT      column-name1 [,column-name2] ...
     FROM        table-name
     [WHERE      search-condition]
     [ORDER BY   column-name [DESC] ] ;

Example: Retrieve a list of departments arranged by division, and within that division, arranged by highest budget first.

If you enter the statement:

SELECT      DNAME, DIV, BUDGET
     FROM        DEPARTMENT
     ORDER BY    DIV, BUDGET DESC ;

Output will appear in ascending order automatically if DESC is omitted.

The result displayed will be:

DNAME                                  DIV     BUDGET
  Corporate Legal Support                CORP    28000
  Finance and Accounting                 CORP    26000
  Management and Administration          CORP    22000
  Recruitment and Personnel              CORP    15000
  Supplies and Procurement               CORP    10500
  Strategic Planning and Intelligence    INFO    28500
  Information Systems Management         INFO    22500
  Corporate Library                      INFO    18500
  Manufacturing                          PROD    32000
  Research and Development               PROD    27500
  Product Marketing and Sales            PROD    25000
  Customer Support and Service           PROD    11000

Notice that all departments in the same division are listed together, with the divisions listed in ascending order, as the default ordering for DIV. Within each division, the department with the highest budget is listed first, since descending order was specified for BUDGET.

It is possible to have as many as 50 sort keys. The order in which the sort keys are listed is the order in which the data will be arranged.

This chapter has introduced several ways to retrieve rows and columns from a table. In the next chapter, you will learn how to perform calculations on data stored in a table.