Kontakt

Ansicht
Dokumentation

ABENSQL_EXPR_OVER_ORDER_BY_ABEXA - SQL EXPR OVER ORDER BY ABEXA

ABENSQL_EXPR_OVER_ORDER_BY_ABEXA - SQL EXPR OVER ORDER BY ABEXA

ROGBILLS - Synchronize billing plans   TXBHW - Original Tax Base Amount in Local Currency  
This documentation is copyright by SAP AG.
SAP E-Book

- Window Expressions with Sort

This example demonstrates window expressions with a sort.

Source Code

Execute

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.






SUBST_MERGE_LIST - merge external lists to one complete list with #if... logic for R3up   SUBST_MERGE_LIST - merge external lists to one complete list with #if... logic for R3up  
This documentation is copyright by SAP AG.

Length: 2714 Date: 20240511 Time: 043523     sap01-206 ( 50 ms )