version 11.2 (Modified)
ORDER BY FORMULA (aTable{; expression{; > or <}}{; expression2; > or <2; ...; expressionN; > or <N})
Parameter | Type | Description | |
aTable | Table | Table for which to order selected records | |
expression | Expression on which to set the order for each level | ||
(can be of type Alphanumeric, Real, Integer, | |||
Long Integer, Date, Time or Boolean) | |||
> or < | Ordering direction for each level: | ||
> to order in ascending order, or | |||
< to order in descending order |
Description
ORDER BY FORMULA 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.
Note that you must specify aTable. You cannot use a default table.
You can sort the selection on one level or on several levels. For each sort level, you specify a expression in expression 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. If you do not specify the sorting order, ascending order is the default.
The parameter expression can be of type Alphanumeric, Real, Integer, Long Integer, Date, Time or Boolean.
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).
4D Server: Beginning with version 11 of 4D Server, this command is executed on the server, which optimizes its execution. Note that when variables are called directly in the expression, the sort is calculated with the value of the variable on the client machine.
On the other hand, this principle does not apply for formulas using methods that, themselves, call variables (the values of the variables are evaluated on the server). In this context, it may be advisable to use the "Execute on server" method attribute that allows a method to be executed on the server while passing parameters (variables) to it (see the Design Reference manual).
In previous versions of 4D Server, this command was executed on the client machines. For compatibility's sake, this functioning is maintained in databases converted to version 11. A compatibility preference and a selector of the SET DATABASE PARAMETER command can nevertheless be used to adopt the functioning of version 11 (execution on the server) in these databases.
Example
This example orders the records of the [People] table in descending order, based on the length of each person's last name. The record for the person with the longest last name will be first in the current selection:
ORDER BY FORMULA ([People]; Length([People]Last Name);<)
See Also