Specifies summary lines and breaks (also known as grouping conditions) that determine how selected results are returned.
The typical use of a WITH clause is with subtotals.
Note: The WITH clause has a different function than the WITH statement modifier. See “WITH Statement Modifier” on page 43.
where:
|
Syntax Element … |
Specifies … |
|
WITH expression_1 |
an introduction to the condition to be fulfilled by the SELECT statement. Specifies a summary line, such as a total, for the values in a column of the select result. expression_1 can contain one or more aggregate expressions that are applied to column values. You cannot include LOB columns in the WITH expression_1 list. You cannot include a WITH clause with NORMALIZE. |
|
BY expression_2 |
one or more result expressions for which expression_1 is provided. BY is valid only when used with WITH. expression_2 can refer to an expression in the select expression list either by name or by means of a constant that specifies the numeric position of the expression in the expression list. It can also refer to a scalar subquery. You cannot include LOB columns in the BY expression_2 list. |
|
ASC |
Results are to be ordered in ascending sort order. If the sort field is a character string, the system orders it in ascending order according to the definition of the collation sequence for the current session. The default order is ASC. If the BY clause is not specified, at that level, there is no ordering of the result. |
|
DESC |
Results are to be ordered in descending sort order. If the sort field is a character string, the system orders it in descending order according to the definition of the collation sequence for the current session. |