4D Doc Center : ORDER BY

4D v13.4

ORDER BY


ORDER BY ( {aTable ;}{ aField }{; > or < }{; aField2 ; > or <2 ; ... ; aFieldN ; > or <N}{; *} )

Parameter	Type	Description
aTable	Table	Table for which to order selected records, or Default table, if omitted
aField	Field	Field on which to set the order for each level
> or <	Operator	Ordering direction for each level: > to order in ascending order, or < to order in descending order
*	Operator	Continue order flag

Description

ORDER BY sorts (reorders) the records of the current selection of aTable for the current process. After the sort has been completed, the new first record of the selection becomes the current record.

If you omit the aTable parameter, the command applies to the default table, if it has been specified. Otherwise, 4D uses the table of the first field passed as a parameter. If you do not pass a parameter and if no default table has been specified, an error is returned.

If you do not specify the aField, the > or < or the * parameters, ORDER BY displays the Order By editor for aTable. The Order By editor is shown here:

For more information about using the Order By editor, refer to the 4D Design Reference manual.

The user builds the sort, then clicks the Sort button to perform the sort. If the sort is performed without interruption, the OK variable is set to 1. If the user clicks Cancel, the ORDER BY terminates with no sort actually performed, and sets the OK variable to 0 (zero).

Example 1

The following line displays the Order By editor for the [Products] table:

ORDER BY([Products])

Example 2

The following line displays the Order By editor for the default table (if it has been set):

ORDER BY

If you specify the aField and > or < parameters, the standard Order By editor is not presented and the sort is defined programmatically. You can sort the selection on one level or on several levels. For each sort level, you specify a field in aField and the sorting order in > or <. If you pass the “greater than” symbol (>), the order is ascending. If you pass the “less than” symbol (<), the order is descending.

Example 3

The following line orders the selection of [Products] by name in ascending order:

ORDER BY([Products];[Products]Name;>)

Example 4

The following line orders the selection of [Products] by name in descending order:

ORDER BY([Products];[Products]Name;<)

Example 5

The following line orders the selection of [Products] by type and price in ascending order for both levels:

ORDER BY([Products];[Products]Type;>;[Products]Price;>)

Example 6

The following line orders the selection of [Products] by type and price in descending order for both levels:

ORDER BY([Products];[Products]Type;<;[Products]Price;<)

Example 7

The following line orders the selection of [Products] by type in ascending order and by price in descending order:

ORDER BY([Products];[Products]Type;>;[Products]Price;<)

Example 8

The following line orders the selection of [Products] by type in descending order and by price in ascending order:

ORDER BY([Products];[Products]Type;<;[Products]Price;>)

If you omit the sorting order parameter > or <, ascending order is the default.

Example 9

The following line orders the selection of [Products] by name in ascending order:

ORDER BY([Products];[Products]Name)

If only one field is specified (one level sort) and it is indexed, the index is used for the order. If the field is not indexed or if there is more than one field, the order is performed sequentially (except in the case of composite indexes). The field may belong to the (selection’s) table being reordered or to a One table related to aTable with an automatic or manual relation. In this case, the sort is always sequential.
If the sorted fields are included in a composite index, ORDER BY uses the index for the order.

Example 10

The following line performs an indexed sort if [Products]Name is indexed:

ORDER BY([Products];[Products]Name;>)

Example 11

The following line performs a sequential sort, whether or not the fields are indexed:

ORDER BY([Products];[Products]Type;>;[Products]Price;>)

Example 12

The following line performs a sequential sort using a related field:

ORDER BY([Invoices];[Companies]Name;>) ` Invoices are sorted alphabetically on the Company name field

Example 13

The following example carries out an indexed sort on two levels if a [Contacts]LastName + [Contacts]FirstName composite index has been specified in the database:

ORDER BY([Contacts];[Contacts]LastName;>;[Contacts]FirstName;>)

For multiple sorts (sorts on multiple fields), you can call ORDER BY as many times as necessary and specify the optional * parameter, except for the last ORDER BY call, which starts the actual sort operation. This feature is useful for multiple sorts management in customized user interfaces.

Warning: with this syntax, you can pass only one sort level (field) per ORDER BY call.

Example 14

In an Output form displayed in the Application environment, you allow the users to order a column in ascending order by simply clicking in the column header.
If the user holds the Shift key down while clicking in other column headers, the sort is performed on several levels:

Each column header contains a highlight button attached with the following object method:

MULTILEVEL(->[CDs]Title) `Title column header button

Each button calls the MULTILEVEL project method with a pointer to the corresponding column field. The MULTILEVEL project method is the following:

  ` MULTILEVEL Project Method

  ` MULTILEVEL (Pointer)

  ` MULTILEVEL (->[Table]Field)

 

 C_POINTER($1)&nbsp;&nbsp; `Sort level (field)

 C_LONGINT($lLevelNb)

 

  `Getting sorting levels

 If(Not(Shift down)) `Simple sort (one-level)

    ARRAY POINTER(aPtrSortField;1)

    aPtrSortField{1}:=$1

 Else

    $lLevelNb:=Find in array(aPtrSortField;$1) `Is this field already sorted?

    If($lLevelNb<0) `If not

       INSERT IN ARRAY(aPtrSortField;Size of array(aPtrSortField)+1;1)

       aPtrSortField{Size of array(aPtrSortField)}:=$1

    End if

 End if

  `Performing the sort

 $lLevelNb:=Size of array(aPtrSortField)

 If($lLevelNb>0) `There is at least one order level

    For($i;1;$lLevelNb)

       ORDER BY([CDs];(aPtrSortField{$i})->;>;*) `Building sort definition

    End for

    ORDER BY([CDs]) `No * ends the sort definition and starts the actual sort operation

 End if

No matter what way a sort has been defined, if the actual sort operation is going to take some time to be performed, 4D automatically displays a message containing a progress thermometer. These messages can be turned on and off by using the commands MESSAGES ON and MESSAGES OFF. If the progress thermometer is displayed, the user can click the Stop button to interrupt the sort. If the sort is completed, OK is set to 1. Otherwise, if the sort is interrupted, OK is set to 0 (zero).