You can use the ORDER BY Clause dialog box to build an ORDER BY clause as part of a multi-row SELECT statement. An ORDER BY clause sorts the rows in a result set by one or more columns or expressions in the SELECT statement.

You cannot build an ORDER BY clause as part of a single-row SELECT statement.

The Asc and Desc push buttons in the ORDER BY Clause dialog box allow you to sort a selected column in ascending or descending order, respectively. By default, DB/Assist sorts the column in ascending order.

If you sort the results by a computed column, DB/Assist displays the column as a number indicating its position in the SELECT statement. For example, the following SELECT statement contains a computed column (shown in italics) in the second position from the left. The computed column expression represents the salary after a five percent increase.

 

SELECT STAFF.NAME, STAFF.SALARY*1.05 FROM DB2ADMIN.STAFF STAFF

 

If you add an ORDER BY clause to this statement, which sorts the results in ascending order by the increased salary, DB/Assist displays the syntax as follows. Notice that instead of displaying the computed column name in the ORDER BY clause, DB/Assist displays the number 2, indicating the computed column's position in the SELECT statement.

 

SELECT STAFF.NAME, STAFF.SALARY*1.05 FROM DB2ADMIN.STAFF STAFF

ORDER BY 2 ASC

 

If you change the position of the computed column (STAFF.SALARY*1.05) to the first column in the SELECT statement, the 2 in the ORDER BY clause does not automatically change to a 1 to reflect this.

To update the ORDER BY clause, you can do either of the following:

•Manually change the ORDER BY clause in the SQL Editor window.

•Return to the ORDER BY Clause dialog box and select the computed column. It will appear in the Order By list with the correct position number.

The procedure in this section illustrates how to use the ORDER BY clause dialog box to do the following:

•Build an ORDER BY clause that includes a computed column.

•Rearrange the position of the ORDER BY columns.

•Change the default sort order of an ORDER BY column.

The following SELECT statement retrieves the name, years, and salary from the Staff table. The ORDER BY clause sorts the results first in ascending order by the increased salary, and then in descending order by the number of years.

 

SELECT STAFF.NAME, STAFF.YEARS, STAFF.SALARY*1.05 FROM DB2ADMIN.STAFF STAFF

ORDER BY 3 ASC, STAFF.YEARS DESC

 

To build the ORDER BY clause (shown in bold) in this statement, follow these steps:

1.Select SQL Statement Ø Order by.
The ORDER BY Clause dialog box appears. The names of the columns you chose in the SELECT/FROM Clause dialog box appear in the Columns list box.

2.Select STAFF.YEARS and STAFF.SALARY*1.05 from the Columns list box, and then select the > push button.
The columns you selected appear in the Order By list box, as shown below.
The "A" to the left of the column name indicates that the default sort order, ascending, is in effect for that column.

ORDER BY Clause Dialog Box

For the computed column, STAFF.SALARY*1.05, DB/Assist displays the number 3. This indicates that the computed column is in the third position from the left in the SELECT statement.

If you need more information about moving columns to or removing columns from the Order By list box, see Selecting Tables and Columns for SELECT UPDATE and INSERT Statements.

1.Make sure that 3 is selected in the Order By list box.

2.Select the Up push button.
The column you selected moves up one position to the top of the Order By list.

3.Select STAFF.YEARS in the Order By list box. (You must deselect 3 in order to select STAFF.YEARS.)

4.Select the Desc push button to change the sort order for this column from ascending to descending.
The "A" to the left of STAFF.YEARS changes to a "D" to indicate that descending sort order is now in effect.
Below shows the completed ORDER BY Clause dialog box for the sample statement.

Completed ORDER BY Clause Dialog Box

1.Select the Apply push button.
DB/Assist adds the ORDER BY clause to the current SELECT statement.

2.Select the Close push button or double-click on the system menu to close the dialog box.

If you do not want to add the ORDER BY clause to the current SELECT statement, select the Close push button at any time before selecting the Apply push button.