Skip to content

ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Operands and Expressions →  ABAP SQL - SQL Expressions sql_exp 

sql_exp - sql_win

Other versions: 7.31 | 7.40 | 7.54

Syntax


... win_func OVER( [PARTITION
sql_exp1, sql_exp2 ...] 

                   [ORDER BY
col1 [ASCENDING|DESCENDING], 

                 
          
col2 [ASCENDING|DESCENDING], ...] ) ...

Addition

... ORDER BY col1 [ASCENDING|DESCENDING], col2 [ASCENDING|DESCENDING], ...

Effect

Window expression in ABAP SQL. A window expression uses a window function to determine an individual value from the rows of a window of the results set of a query. A window expression can be specified as an SQL expression or as an argument of any SQL expressions, except for aggregate and window expressions for defining a column col_spec of the result set in the SELECT list of a query. In each row of the result set, the result of the calculation is placed in the column defined in this way.

A window expression consists of a window function win_func followed by the addition OVER( ... ) in whose parentheses the window on the results set is defined for whose rows the window function is evaluated:

The optional addition PARTITION defines the windows using a comma-separated list of SQL expressions sql_exp1, sql_exp2, ... . A window is formed by the rows of the result set for which all SQL expressions have the same result. All SQL expressions except for aggregate expressions and window expressions can be used. The window function is calculated for the rows of the respective current window. If PARTITION is not specified, a single window is formed, which comprises all rows of the result set.
  • ORDER BY
The optional addition ORDER BY introduces both an order and a frame within the current window, which further restricts the rows for which the window function is calculated. It is also a prerequisite for certain ranking functions.

If a window expression is used in the SELECT list of a SELECT statement with GROUP BY clause, the windows are defined in the consolidated result list and aggregate expressions can be used as arguments of the window features. Each column that is specified in any position in the window expression must also be specified in the GROUP BY clause.


Notes

  • A window expression works in a similar way to an aggregate expression, whereby the addition PARTITION assumes the role of the GROUP BY clause. In contrast with the aggregate expressions, however, there is no aggregation of the rows defined via PARTITION, rather these are retained and are all assigned the value calculated via the window expression.

  • The window features of a window expression work, as it were, with the rows of a virtual table, which is defined by the specifications after OVER. Using the addition ORDER BY of the SELECT statement does not influence the result of a window expression. However, the rows of the result set can be sorted according to the results of window expressions through using their alias name.

  • By using window expressions in other SQL expressions, calculations can be performed, which combine values of the current row with the results of window expressions, for example, the percentage of a column in the current window, or the distance to the minimum or maximum value of the current window.

  • Window expressions are not supported by all databases. In an ABAP program, it is possible to use the method USE_FEATURES of the class CL_ABAP_DBFEATURES to check whether the current database system or a database system accessed using a secondary connection supports the use of window expressions. This requires the constant WINDOWING of this class to be passed to the method in an internal table.

Executable Examples

Addition

... ORDER BY col1 [ASCENDING|DESCENDING], col2 [ASCENDING|DESCENDING], ...

Effect

The optional addition ORDER BY, which can be specified independently of PARTITION, defines an order in the current window and an evaluation framework for the window function. ORDER BY is followed by a comma-separated list of columns of the data sources of the current query. These columns are used to sort the rows of the window. A column can only be specified directly using the column name col1, col2 ... Alias names defined using AS cannot be specified. The additions ASCENDING and DESCENDING determine whether the rows are sorted in ascending or descending order by the column in question. The default is ASCENDING.

Specifying the order using ORDER BY causes the following:

  • The rows of the window are processed by the window function in the order defined by the sort sequence. The order of the processing of rows that appear more than once regarding the sort criterion is not defined. If the addition ORDER BY is not specified, this applies to all rows of the window.
  • The rows processed by the window function are additionally restricted by a frame. Only the rows of the window that are in front of the current row in the sorting, or that have the same values in the columns of the sort criterion are taken into account.

While the restricted frame mainly affects the results of aggregate functions specified as a window function, the processing sequence primarily affects the ranking functions. The addition ORDER BY must be specified for the ranking functions RANK and DENSE_RANK.


Note

The definition of the window, processing sequence, and the frame by ORDER BY following OVER is totally independent of the addition ORDER BY clause of the SELECT statement.

Executable Example

Window Expressions with Sort

Continue

sql_exp - win_func

ABAP SQL - Examples of Window Expressions