SAP SQL EXPR OVER ORDER BY ABEXA
Get Example source ABAP code based on a different SAP table
ABAP_SQLWIN - Window Expressions with Sort This example demonstrates
window expressions with a sort.
ABAP_SOURCE_CODE
DEMO CL_DEMO_SELECT_OVER_ORDER_BY
ABAP_DESCRIPTION
The example compares SELECT statements with identical window expressions with and without the addition ORDER BY specified after OVER. The use of the ranking functions RANK and DENSE_RANK is only possible in combination with this addition.
If the addition ORDER BY is not specified after OVER, the window functions are executed in an undefined order for all rows of the windows defined by the content of the column CHAR1:
The aggregate functions COUNT, MAX, MIN, and SUM evaluate all rows of a window.
The ranking function ROW_NUMBER assigns a sequential row number within each window, which matches the undefined order of processing.
When the addition ORDER BY is specified after OVER the window functions within the window are executed once in ascending order and once in descending order, with respect to the content of the column CHAR2. Only the rows of a frame are respected:
Within a window, that is, all rows with identical content in the column CHAR1, the aggregate functions COUNT, MAX , MIN, and SUM evaluate only those rows that have identical content in CHAR2 or that precede it in the sort.
The ranking function ROW_NUMBER assigns the row number in accordance with the sort order. The order is then only undefined in rows with identical content in the column CHAR2. The ranking functions RANK and DENSE_RANK return the rank with respect to the sort order within the current window. Here, identical content in the column CHAR2 has the same rank.
The addition ORDER BY of the SELECT statement is used only to highlight the output. It does not modify the results for the individual rows.
