Creating ORDER BY clauses for multiple-column sorts in FULLSELECT statements in the SQL builder

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

When 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.

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

To sort on columns in a FULLSELECT statement that have the same ordinal value but are in different subordinate SELECT statements, the columns must have the same name. If the columns have different names in their corresponding subordinate SELECT statements, you must assign the same alias to each of the columns.

Prerequisites:
  • The FULLSELECT statement must be open in the SQL builder.
  • The tables must be specified for the FULLSELECT statement.

To create a ORDER BY clause for a multiple-column sort in a FULLSELECT statement in the SQL builder:

  1. If the columns on which to sort have different names in different SELECT statements, specify an alias for the columns. Use the same alias for each of the columns.
    1. In the Outline view, click a subordinate SELECT statement that contains a column on which to sort.
    2. In the Tables pane of the SQL builder, select the check boxes of the columns that you want to use in the statement.
    3. On the Columns page in the Design pane of the SQL builder, select the column on which to sort, and then double-click its Alias cell.
    4. Type an alias for the column, and then press Enter.
  2. In the Outline view, select the root-node SELECT statement or a UNION node that represents a subordinate FULLSELECT statement.
  3. On the Columns page in the Design pane of the SQL builder, double-click the first cell under the Column heading to open the list of available columns.
  4. Select the first column on which to sort. This is the first column to which you added the alias.
  5. Double-click the Sort Type cell, select either Ascending, Descending, or Default, and then press Enter. 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.
  6. Double-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.
  8. Repeat these steps with other columns.
Related tasks
Creating ORDER BY clauses for single-column sorts in FULLSELECT statements in the SQL builder

Feedback