.Sort Statement--Specify Sort Order of Reported Data
The .sort statement specifies the ordering of rows to be reported.
The .sort statement has the following format:
.sort | .srt {columnname[:sortorder] {, columnname[:sortorder]}}
The parameters for the .sort statement are as follows:
columnname
The name of a column in the table to be reported, or the label for a column in the result column list of the specified query. You can express columnname as a delimited identifier by enclosing it in double quotes ("), if you have previously specified the .delimid statement.
The columnname can be expressed as a variable. The colon (:) and the comma (,) must be explicitly stated and cannot be part of the sortorder variable.
sortorder
Either ascending (also a or asc or ascend) or descending (also d or desc or descend), depending on how you want the rows to be ordered. If neither is specified, the default is ascending.
The sortorder can be expressed as a variable. The colon (:) and the comma (,) must be explicitly stated and cannot be part of the sortorder variable.
$columnvariable
The variable whose value is the name of a column. Precede the variable with a dollar sign ($).
$sortorder
The variable whose value is a sort order that evaluates to an acceptable sort direction. For more information, see the description of sortorder in this table. Precede the variable with a dollar sign ($).
Description
The optional .sort statement specifies the ordering that applies to the rows of data to be reported. Report-Writer initially sorts rows on the first column in the list. If several rows have the same value in the first sort column, Report-Writer then sorts them on the second column in the list, and so forth. If there is exactly one sort column, and there are duplicate values for the sort column, all rows with that value appears together, but in an undetermined order relative to each other.
By default, the .sort statement also specifies the columns used as break columns in the report. You can specify break headers and footers for each column, using the .header and .footer statements. A break on one column in the sort list produces a break on all subsequent columns in the sort list.
If you want, you can use the .sort statement to order rows for appearance in the report only, without specifying these columns as break columns. To override the default break specifications in the .sort statement, specify break columns in a .break statement.
An SQL language report specification includes duplicate rows in the data for reports that use the .sort statements. To specify distinct rows, you can specify the ‑6 flag on the report command line.
If you specify variables as the columnname and/or sortorder parameters, Report-Writer evaluates these variables during the loading of the report specification, before retrieving the data.
You can have either a .sort statement or an order by clause in a .query statement but not both in a report specification.
Note: When using a variable for columnname, specify the same variable identically in corresponding .break, .header, and .footer statements.
Examples
1. Sort two columns of a table (whose names are specified by delimited identifiers), with both columns in ascending order:
.sort "last name","first name"
2. Sort three columns of a table, with first and last columns in descending order, and the second column in ascending order.
.sort dept:descending,
jobcode,
name:d
3. Sort the column specified by the value of the variable sort_col in the order specified by the value of the variable sort_order. For example, the value of sort_col might be last_name and the value of sort_order might be for ascending.
.sort $sort_col:$sort_order
...
.header $sort_col
...
.out \direct\subdirect\myreport.lis
.include \direct\subdirect\otherrep.rw
Last modified date: 08/28/2024