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]

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 ;

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.