Creating an ORDER BY clause in a FULLSELECT statement

The ORDER BY clause specifies the order of the rows of a results table for an SQL statement.

If a single sort specification is identified, the rows are ordered by the values of that sort specification. If more than one sort specification is identified, the rows are ordered by the values of the first indicated sort specification, then by the values of the second identified sort specification, and so on. The ORDER BY clause can be set for both SELECT and FULLSELECT statements.

You cannot have an ORDER BY clause on SELECT statements within the FULLSELECT statement. The ORDER BY applies to the final result set from the FULLSELECT statement. You can sort on either a single column or multiple columns.

To create an ORDER BY clause in a FULLSELECT statement:

1. Switch to the Data perspective.

2. Create the FULLSELECT statement.

3. Open the FULLSELECT statement in the SQL Builder.

4. Add SELECT statements as required.

5. To sort on a single column, do the following:
   1. Make sure you are in the FULLSELECT editor pane by clicking the FULLSELECT statement in the Outline view.

   2. In the SQL Builder, click the first cell under the Column heading, and then click again to open the list of available columns.

   3. Click the column or enter a column expression on which you want to sort.

   4. Click the Sort Type cell, and then select either Ascending, Descending, or Default. The Default option indicates that sorting is required, but no SQL source should be shown for the sort type. The default sort type for your database product is used.

   5. Press Enter

   6. Click the cell under the Sort Order heading.

   7. Select the number that corresponds to the order in which to sort the results, and then press Enter.

6. To sort on multiple columns, do the following. To sort on columns in a FULLSELECT statement that have the same ordinal value but are in different sub-SELECT statements, the columns must have the same name. If the columns have different names in their corresponding sub-SELECT statements, assign the same alias to each of the columns.
   1. In the Outline view, click the SELECT statement that contains on which to sort.

   2. In the Tables pane, select the check boxes beside the columns to use in your statement.

   3. On the Columns page in the SQL Builder, select the column on which to sort, and then click the Alias cell.

   4. Type an alias for this column, and then press Enter.

   5. Repeat these steps for the other columns on which to sort in the other sub-SELECT statements. Make sure that you use the same alias for each of the multiple columns. After you have defined all the columns on which to start, you are ready to create the ORDER BY clause.

   6. In the Outline view, select the FULLSELECT statement.

   7. On the Columns page in the SQL Builder, click the first cell under the Column heading, and then click again to open the list of available columns.

   8. Select the first column on which to sort. This is the first column to which you added the alias.

   9. Click the Sort Type cell, and select either Ascending, Descending, or Default. The Default option indicates that sorting is required, but no SQL source should be shown for the sort type. The default sort type for your database product is used.

   10. Press Enter

   11. Click the cell under the Sort Order heading.

   12. Select the number that corresponds to the order in which to sort the results, and then press Enter.

   13. Repeat these steps with other columns until you have created the sort order for the statement results.

 

Parent topic

Creating a FULLSELECT statement