If like me you don’t know well the ABAP SQL window functions (partitions with OVER, window functions) and have difficulties to quickly choose between them, here’s a compilation of the ABAP Keyword Documentation + ABAP results. It concerns ABAP 7.58.
AVG (ask me if you need it)CORR (ask)CORR_SPEARMAN (ask)COUNTDENSE_RANKFIRST_VALUELAGLAST_VALUELEADMAXMEDIAN (ask)MINNTILERANKROW_NUMBERSTDDEV (ask)VAR (ask)
FIRST_VALUE, LAST_VALUE
Content of table DEMO_UPDATE:
IDCOL1COL2COL3A1562B1462C1862D11062E197862F192788G1099H10124I2100144J24344K2444L21744M23344N210044O210144P2583955Q254877R218133S355434623T31001111
ABAP SQL:
SELECT
id,
col1,
col2,
col3,
FIRST_VALUE( col2 ) OVER( PARTITION BY col1 ORDER BY col3 )
AS first_value,
LAST_VALUE( col2 ) OVER( PARTITION BY col1 ORDER BY col3 )
AS last_value,
LAST_VALUE( col2 ) OVER( PARTITION BY col1 ORDER BY col3
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING )
AS last_value_correct
FROM demo_update
INTO TABLE @FINAL(result).
Result and explanations (copied from ABAP Keyword Documentation):
The class CL_DEMO_SELECT_FIRST_LAST divides the rows from the DEMO_UPDATE table into three partitions (orange, red, purple), depending on their value in COL1. Within the partitions, the rows are ordered by their value in COL3.
The column FIRST_VALUE returns the first value of COL2 for each partition.
The column LAST_VALUE does not return the last value. With the LAST_VALUE function, framing is an important aspect to consider. The default frame is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so the LAST_VALUE function always returns the value from the current row. To find the last value for a partition or a window, the correct frame has to be specified explicitly: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING as shown in LAST_VALUE_CORRECT. If COL3 contains duplicate values, the rows are considered equal and the last value from the group of equals is returned.
In this example, COL3 has multiple duplicate values. The key field – here the field ID – is used to sort rows with the same value.
LAG, LEAD
Content of table DEMO_EXPRESSIONS:
IDCHAR1CHAR2NUM1bAAAA6dAAAA3eAAAA4tAAAA8cAAAB6vAAAB1aAABA5fAABA8kABBB2rABBB4nBAAA7uBAAB4yBAAB10oBABA6xBABA10iBABB9mBABB9pBABB6sBABB6gBBAB5lBBAB4qBBAB4wBBAB1hBBBA7jBBBB6
ABAP SQL:
SELECT char1, char2,
num1,
ROW_NUMBER( )
OVER( PARTITION BY char1 ORDER BY char2 )
AS rnum,
LEAD( CAST( num1 AS CHAR( 11 ) ), 1, ‘Nope’ )
OVER( PARTITION BY char1 ORDER BY char2 )
AS lead1,
LAG( CAST( num1 AS CHAR( 11 ) ), 1, ‘Nope’ )
OVER( PARTITION BY char1 ORDER BY char2 )
AS lag1,
LEAD( CAST( num1 AS CHAR( 11 ) ), 2, ‘Nope’ )
OVER( PARTITION BY char1 ORDER BY char2 )
AS lead2,
LAG( CAST( num1 AS CHAR( 11 ) ), 2, ‘Nope’ )
OVER( PARTITION BY char1 ORDER BY char2 )
AS lag2,
LEAD( CAST( num1 AS CHAR( 11 ) ), 10, ‘Nope’ )
OVER( PARTITION BY char1 ORDER BY char2 )
AS lead10,
LAG( CAST( num1 AS CHAR( 11 ) ), 10, ‘Nope’ )
OVER( PARTITION BY char1 ORDER BY char2 )
AS lag10
FROM demo_expressions
ORDER BY char1, char2 “#EC CI_NOWHERE
INTO TABLE @FINAL(windowed_order_ascending) ##no_text.
Result:
CHAR1CHAR2NUM1RNUMLEAD1LAG1LEAD2LAG2LEAD10LAG10AAAA613Nope4NopeNopeNopeAAAA32468NopeNopeNopeAAAA438366NopeNopeAAAA846413NopeNopeAAAB651854NopeNopeAAAB165688NopeNopeAABA5781Nope6NopeNopeAABA88Nope5Nope1NopeNopeABBB214NopeNopeNopeNopeNopeABBB42Nope2NopeNopeNopeNopeBAAA714Nope10NopeNopeNopeBAAB421076NopeNopeNopeBAAB10364107NopeNopeBABA64101094NopeNopeBABA10596910NopeNopeBABB9691066NopeNopeBABB9769610NopeNopeBABB6869Nope9NopeNopeBABB69Nope6Nope9NopeNopeBBAB514Nope4NopeNopeNopeBBAB42451NopeNopeNopeBBAB431475NopeNopeBBAB147464NopeNopeBBBA7561Nope4NopeNopeBBBB66Nope7Nope1NopeNope
ABAP SQL:
SELECT char1, char2,
num1,
ROW_NUMBER( )
OVER( PARTITION BY char1 ORDER BY char2 DESCENDING )
AS rnum,
LEAD( CAST( num1 AS CHAR( 11 ) ), 1, ‘Nope’ )
OVER( PARTITION BY char1 ORDER BY char2 DESCENDING )
AS lead1,
LAG( CAST( num1 AS CHAR( 11 ) ), 1, ‘Nope’ )
OVER( PARTITION BY char1 ORDER BY char2 DESCENDING )
AS lag1,
LEAD( CAST( num1 AS CHAR( 11 ) ), 2, ‘Nope’ )
OVER( PARTITION BY char1 ORDER BY char2 DESCENDING )
AS lead2,
LAG( CAST( num1 AS CHAR( 11 ) ), 2, ‘Nope’ )
OVER( PARTITION BY char1 ORDER BY char2 DESCENDING )
AS lag2,
LEAD( CAST( num1 AS CHAR( 11 ) ), 10, ‘Nope’ )
OVER( PARTITION BY char1 ORDER BY char2 DESCENDING )
AS lead10,
LAG( CAST( num1 AS CHAR( 11 ) ), 10, ‘Nope’ )
OVER( PARTITION BY char1 ORDER BY char2 DESCENDING )
AS lag10
FROM demo_expressions
ORDER BY char1, char2 “#EC CI_NOWHERE
INTO TABLE @FINAL(windowed_order_descending) ##no_text.
Result:
CHAR1CHAR2NUM1RNUMLEAD1LAG1LEAD2LAG2LEAD10LAG10AAAA653146NopeNopeAAAA364681NopeNopeAAAA4783Nope6NopeNopeAAAA88Nope4Nope3NopeNopeAAAB631865NopeNopeAAAB146638NopeNopeAABA518Nope6NopeNopeNopeAABA82651NopeNopeNopeABBB214NopeNopeNopeNopeNopeABBB42Nope2NopeNopeNopeNopeBAAA79Nope10Nope4NopeNopeBAAB47101076NopeNopeBAAB10874Nope10NopeNopeBABA6510646NopeNopeBABA10646106NopeNopeBABB919Nope6NopeNopeNopeBABB92696NopeNopeNopeBABB636969NopeNopeBABB6466109NopeNopeBBAB534746NopeNopeBBAB444517NopeNopeBBAB4514Nope5NopeNopeBBAB16Nope4Nope4NopeNopeBBBA72564NopeNopeNopeBBBB617Nope5NopeNopeNope
NTILE
Content of table DEMO_EMPLOYEES:
NAMESALARYLothar Sudhoff1354Johannes Legrand1468Holm Trensch2324Johann Buchholm3245Christa Martin3247Laura Lindwurm3247Amelie Babilon3485Thilo Eichbaum5436Ulla Babilon5489Illya Gueldenpfennig7453Anna Picard7453
ABAP SQL:
SELECT name,
salary,
NTILE( 5 ) OVER( ORDER BY salary ) AS ntile
FROM demo_employees
INTO TABLE @FINAL(result).
Result:
NAMESALARYNTILELothar Sudhoff13541Johannes Legrand14681Holm Trensch23241Johann Buchholm32452Christa Martin32472Laura Lindwurm32473Amelie Babilon34853Thilo Eichbaum54364Ulla Babilon54894Illya Gueldenpfennig74535Anna Picard74535
count, dense_rank, max, min, rank, row_number, sum
Content of table DEMO_EXPRESSIONS:
IDCHAR1CHAR2NUM1gAAAB3iAAAB7lAAAB6pAAAB6cAABA10aAABB7bAABB10eABAB4mABAB8wABAB1rABBA1fABBB9nABBB1sABBB4uABBB7hBAAA2xBAAA8oBAAB10dBABA6vBABB8yBABB1kBBAA7jBBBA2tBBBA4qBBBB10
ABAP SQL:
SELECT char1, char2,
num1,
COUNT(*) OVER( PARTITION BY char1 ) AS cnt,
ROW_NUMBER( ) OVER( PARTITION BY char1 ) AS rnum,
‘-‘ AS rank,
‘-‘ AS schlank,
MAX( num1 ) OVER( PARTITION BY char1 ) AS max,
MIN( num1 ) OVER( PARTITION BY char1 ) AS min,
SUM( num1 ) OVER( PARTITION BY char1 ) AS sum,
division( 100 * num1,
SUM( num1 ) OVER( PARTITION BY char1 ),
2 ) AS perc
FROM demo_expressions
ORDER BY char1, char2
INTO TABLE @FINAL(windowed_no_order).
Result:
CHAR1CHAR2NUM1CNTRNUMRANKSCHLANKMAXMINSUMPERCAAAB374––103496.12AAAB775––1034914.29AAAB676––1034912.24AAAB677––1034912.24AABA1073––1034920.41AABB771––1034914.29AABB1072––1034920.41ABAB481––913511.43ABAB883––913522.86ABAB188––91352.86ABBA185––91352.86ABBB982––913525.71ABBB184––91352.86ABBB486––913511.43ABBB787––913520.0BAAA262––101355.71BAAA865––1013522.86BAAB1063––1013528.57BABA661––1013517.14BABB864––1013522.86BABB166––101352.86BBAA742––1022330.43BBBA241––102238.7BBBA444––1022317.39BBBB1043––1022343.48
ABAP SQL:
SELECT char1, char2,
num1,
COUNT(*) OVER( PARTITION BY char1
ORDER BY char2 ) AS cnt,
ROW_NUMBER( ) OVER( PARTITION BY char1
ORDER BY char2 ) AS rnum,
RANK( ) OVER( PARTITION BY char1
ORDER BY char2 ) AS rank,
DENSE_RANK( ) OVER( PARTITION BY char1
ORDER BY char2 ) AS schlank,
MAX( num1 ) OVER( PARTITION BY char1
ORDER BY char2 ) AS max,
MIN( num1 ) OVER( PARTITION BY char1
ORDER BY char2 ) AS min,
SUM( num1 ) OVER( PARTITION BY char1
ORDER BY char2 ) AS sum,
division( 100 * num1,
SUM( num1 ) OVER( PARTITION BY char1
ORDER BY char2 ),
2 ) AS perc
FROM demo_expressions
ORDER BY char1, char2
INTO TABLE @FINAL(windowed_order_ascending).
Result:
CHAR1CHAR2NUM1CNTRNUMRANKSCHLANKMAXMINSUMPERCAAAB34111732213.64AAAB74211732231.82AAAB64311732227.27AAAB64411732227.27AABA1055521033231.25AABB776631034914.29AABB1077631034920.41ABAB43111811330.77ABAB83211811361.54ABAB1331181137.69ABBA1444281147.14ABBB98553913525.71ABBB1865391352.86ABBB48753913511.43ABBB78853913520.0BAAA22111821020.0BAAA82211821080.0BAAB1033321022050.0BABA644431022623.08BABB865541013522.86BABB16654101352.86BBAA71111777100.0BBBA23222721315.38BBBA43322721330.77BBBB1044431022343.48
ABAP SQL:
SELECT char1, char2,
num1,
COUNT(*) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ) AS cnt,
ROW_NUMBER( ) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ) AS rnum,
RANK( ) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ) AS rank,
DENSE_RANK( ) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ) AS schlank,
MAX( num1 ) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ) AS max,
MIN( num1 ) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ) AS min,
SUM( num1 ) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ) AS sum,
division( 100 * num1,
SUM( num1 ) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ),
2 ) AS perc
FROM demo_expressions
ORDER BY char1 DESCENDING, char2 DESCENDING
INTO TABLE @FINAL(windowed_order_descending).
Result:
CHAR1CHAR2NUM1CNTRNUMRANKSCHLANKMAXMINSUMPERCBBBB101111101010100.0BBBA232221021612.5BBBA433221021625.0BBAA744431022330.43BABB8211181988.89BABB1221181911.11BABA63332811540.0BAAB1044431012540.0BAAA26554101355.71BAAA866541013522.86ABBB94111912142.86ABBB1421191214.76ABBB44311912119.05ABBB74411912133.33ABBA1555291224.55ABAB48663913511.43ABAB88763913522.86ABAB1886391352.86AABB721111071741.18AABB1022111071758.82AABA1033321072737.04AAAB37443103496.12AAAB775431034914.29AAAB676431034912.24AAAB677431034912.24
If like me you don’t know well the ABAP SQL window functions (partitions with OVER, window functions) and have difficulties to quickly choose between them, here’s a compilation of the ABAP Keyword Documentation + ABAP results. It concerns ABAP 7.58.AVG (ask me if you need it)CORR (ask)CORR_SPEARMAN (ask)COUNTDENSE_RANKFIRST_VALUELAGLAST_VALUELEADMAXMEDIAN (ask)MINNTILERANKROW_NUMBERSTDDEV (ask)VAR (ask)FIRST_VALUE, LAST_VALUEContent of table DEMO_UPDATE:IDCOL1COL2COL3A1562B1462C1862D11062E197862F192788G1099H10124I2100144J24344K2444L21744M23344N210044O210144P2583955Q254877R218133S355434623T31001111ABAP SQL:SELECT
id,
col1,
col2,
col3,
FIRST_VALUE( col2 ) OVER( PARTITION BY col1 ORDER BY col3 )
AS first_value,
LAST_VALUE( col2 ) OVER( PARTITION BY col1 ORDER BY col3 )
AS last_value,
LAST_VALUE( col2 ) OVER( PARTITION BY col1 ORDER BY col3
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING )
AS last_value_correct
FROM demo_update
INTO TABLE @FINAL(result). Result and explanations (copied from ABAP Keyword Documentation): The class CL_DEMO_SELECT_FIRST_LAST divides the rows from the DEMO_UPDATE table into three partitions (orange, red, purple), depending on their value in COL1. Within the partitions, the rows are ordered by their value in COL3.The column FIRST_VALUE returns the first value of COL2 for each partition.The column LAST_VALUE does not return the last value. With the LAST_VALUE function, framing is an important aspect to consider. The default frame is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so the LAST_VALUE function always returns the value from the current row. To find the last value for a partition or a window, the correct frame has to be specified explicitly: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING as shown in LAST_VALUE_CORRECT. If COL3 contains duplicate values, the rows are considered equal and the last value from the group of equals is returned.In this example, COL3 has multiple duplicate values. The key field – here the field ID – is used to sort rows with the same value.LAG, LEADContent of table DEMO_EXPRESSIONS:IDCHAR1CHAR2NUM1bAAAA6dAAAA3eAAAA4tAAAA8cAAAB6vAAAB1aAABA5fAABA8kABBB2rABBB4nBAAA7uBAAB4yBAAB10oBABA6xBABA10iBABB9mBABB9pBABB6sBABB6gBBAB5lBBAB4qBBAB4wBBAB1hBBBA7jBBBB6ABAP SQL: SELECT char1, char2,
num1,
ROW_NUMBER( )
OVER( PARTITION BY char1 ORDER BY char2 )
AS rnum,
LEAD( CAST( num1 AS CHAR( 11 ) ), 1, ‘Nope’ )
OVER( PARTITION BY char1 ORDER BY char2 )
AS lead1,
LAG( CAST( num1 AS CHAR( 11 ) ), 1, ‘Nope’ )
OVER( PARTITION BY char1 ORDER BY char2 )
AS lag1,
LEAD( CAST( num1 AS CHAR( 11 ) ), 2, ‘Nope’ )
OVER( PARTITION BY char1 ORDER BY char2 )
AS lead2,
LAG( CAST( num1 AS CHAR( 11 ) ), 2, ‘Nope’ )
OVER( PARTITION BY char1 ORDER BY char2 )
AS lag2,
LEAD( CAST( num1 AS CHAR( 11 ) ), 10, ‘Nope’ )
OVER( PARTITION BY char1 ORDER BY char2 )
AS lead10,
LAG( CAST( num1 AS CHAR( 11 ) ), 10, ‘Nope’ )
OVER( PARTITION BY char1 ORDER BY char2 )
AS lag10
FROM demo_expressions
ORDER BY char1, char2 “#EC CI_NOWHERE
INTO TABLE @FINAL(windowed_order_ascending) ##no_text. Result:CHAR1CHAR2NUM1RNUMLEAD1LAG1LEAD2LAG2LEAD10LAG10AAAA613Nope4NopeNopeNopeAAAA32468NopeNopeNopeAAAA438366NopeNopeAAAA846413NopeNopeAAAB651854NopeNopeAAAB165688NopeNopeAABA5781Nope6NopeNopeAABA88Nope5Nope1NopeNopeABBB214NopeNopeNopeNopeNopeABBB42Nope2NopeNopeNopeNopeBAAA714Nope10NopeNopeNopeBAAB421076NopeNopeNopeBAAB10364107NopeNopeBABA64101094NopeNopeBABA10596910NopeNopeBABB9691066NopeNopeBABB9769610NopeNopeBABB6869Nope9NopeNopeBABB69Nope6Nope9NopeNopeBBAB514Nope4NopeNopeNopeBBAB42451NopeNopeNopeBBAB431475NopeNopeBBAB147464NopeNopeBBBA7561Nope4NopeNopeBBBB66Nope7Nope1NopeNopeABAP SQL: SELECT char1, char2,
num1,
ROW_NUMBER( )
OVER( PARTITION BY char1 ORDER BY char2 DESCENDING )
AS rnum,
LEAD( CAST( num1 AS CHAR( 11 ) ), 1, ‘Nope’ )
OVER( PARTITION BY char1 ORDER BY char2 DESCENDING )
AS lead1,
LAG( CAST( num1 AS CHAR( 11 ) ), 1, ‘Nope’ )
OVER( PARTITION BY char1 ORDER BY char2 DESCENDING )
AS lag1,
LEAD( CAST( num1 AS CHAR( 11 ) ), 2, ‘Nope’ )
OVER( PARTITION BY char1 ORDER BY char2 DESCENDING )
AS lead2,
LAG( CAST( num1 AS CHAR( 11 ) ), 2, ‘Nope’ )
OVER( PARTITION BY char1 ORDER BY char2 DESCENDING )
AS lag2,
LEAD( CAST( num1 AS CHAR( 11 ) ), 10, ‘Nope’ )
OVER( PARTITION BY char1 ORDER BY char2 DESCENDING )
AS lead10,
LAG( CAST( num1 AS CHAR( 11 ) ), 10, ‘Nope’ )
OVER( PARTITION BY char1 ORDER BY char2 DESCENDING )
AS lag10
FROM demo_expressions
ORDER BY char1, char2 “#EC CI_NOWHERE
INTO TABLE @FINAL(windowed_order_descending) ##no_text. Result:CHAR1CHAR2NUM1RNUMLEAD1LAG1LEAD2LAG2LEAD10LAG10AAAA653146NopeNopeAAAA364681NopeNopeAAAA4783Nope6NopeNopeAAAA88Nope4Nope3NopeNopeAAAB631865NopeNopeAAAB146638NopeNopeAABA518Nope6NopeNopeNopeAABA82651NopeNopeNopeABBB214NopeNopeNopeNopeNopeABBB42Nope2NopeNopeNopeNopeBAAA79Nope10Nope4NopeNopeBAAB47101076NopeNopeBAAB10874Nope10NopeNopeBABA6510646NopeNopeBABA10646106NopeNopeBABB919Nope6NopeNopeNopeBABB92696NopeNopeNopeBABB636969NopeNopeBABB6466109NopeNopeBBAB534746NopeNopeBBAB444517NopeNopeBBAB4514Nope5NopeNopeBBAB16Nope4Nope4NopeNopeBBBA72564NopeNopeNopeBBBB617Nope5NopeNopeNopeNTILEContent of table DEMO_EMPLOYEES:NAMESALARYLothar Sudhoff1354Johannes Legrand1468Holm Trensch2324Johann Buchholm3245Christa Martin3247Laura Lindwurm3247Amelie Babilon3485Thilo Eichbaum5436Ulla Babilon5489Illya Gueldenpfennig7453Anna Picard7453ABAP SQL: SELECT name,
salary,
NTILE( 5 ) OVER( ORDER BY salary ) AS ntile
FROM demo_employees
INTO TABLE @FINAL(result).Result:NAMESALARYNTILELothar Sudhoff13541Johannes Legrand14681Holm Trensch23241Johann Buchholm32452Christa Martin32472Laura Lindwurm32473Amelie Babilon34853Thilo Eichbaum54364Ulla Babilon54894Illya Gueldenpfennig74535Anna Picard74535count, dense_rank, max, min, rank, row_number, sumContent of table DEMO_EXPRESSIONS:IDCHAR1CHAR2NUM1gAAAB3iAAAB7lAAAB6pAAAB6cAABA10aAABB7bAABB10eABAB4mABAB8wABAB1rABBA1fABBB9nABBB1sABBB4uABBB7hBAAA2xBAAA8oBAAB10dBABA6vBABB8yBABB1kBBAA7jBBBA2tBBBA4qBBBB10ABAP SQL: SELECT char1, char2,
num1,
COUNT(*) OVER( PARTITION BY char1 ) AS cnt,
ROW_NUMBER( ) OVER( PARTITION BY char1 ) AS rnum,
‘-‘ AS rank,
‘-‘ AS schlank,
MAX( num1 ) OVER( PARTITION BY char1 ) AS max,
MIN( num1 ) OVER( PARTITION BY char1 ) AS min,
SUM( num1 ) OVER( PARTITION BY char1 ) AS sum,
division( 100 * num1,
SUM( num1 ) OVER( PARTITION BY char1 ),
2 ) AS perc
FROM demo_expressions
ORDER BY char1, char2
INTO TABLE @FINAL(windowed_no_order). Result:CHAR1CHAR2NUM1CNTRNUMRANKSCHLANKMAXMINSUMPERCAAAB374–103496.12AAAB775–1034914.29AAAB676–1034912.24AAAB677–1034912.24AABA1073–1034920.41AABB771–1034914.29AABB1072–1034920.41ABAB481–913511.43ABAB883–913522.86ABAB188–91352.86ABBA185–91352.86ABBB982–913525.71ABBB184–91352.86ABBB486–913511.43ABBB787–913520.0BAAA262–101355.71BAAA865–1013522.86BAAB1063–1013528.57BABA661–1013517.14BABB864–1013522.86BABB166–101352.86BBAA742–1022330.43BBBA241–102238.7BBBA444–1022317.39BBBB1043–1022343.48ABAP SQL: SELECT char1, char2,
num1,
COUNT(*) OVER( PARTITION BY char1
ORDER BY char2 ) AS cnt,
ROW_NUMBER( ) OVER( PARTITION BY char1
ORDER BY char2 ) AS rnum,
RANK( ) OVER( PARTITION BY char1
ORDER BY char2 ) AS rank,
DENSE_RANK( ) OVER( PARTITION BY char1
ORDER BY char2 ) AS schlank,
MAX( num1 ) OVER( PARTITION BY char1
ORDER BY char2 ) AS max,
MIN( num1 ) OVER( PARTITION BY char1
ORDER BY char2 ) AS min,
SUM( num1 ) OVER( PARTITION BY char1
ORDER BY char2 ) AS sum,
division( 100 * num1,
SUM( num1 ) OVER( PARTITION BY char1
ORDER BY char2 ),
2 ) AS perc
FROM demo_expressions
ORDER BY char1, char2
INTO TABLE @FINAL(windowed_order_ascending). Result:CHAR1CHAR2NUM1CNTRNUMRANKSCHLANKMAXMINSUMPERCAAAB34111732213.64AAAB74211732231.82AAAB64311732227.27AAAB64411732227.27AABA1055521033231.25AABB776631034914.29AABB1077631034920.41ABAB43111811330.77ABAB83211811361.54ABAB1331181137.69ABBA1444281147.14ABBB98553913525.71ABBB1865391352.86ABBB48753913511.43ABBB78853913520.0BAAA22111821020.0BAAA82211821080.0BAAB1033321022050.0BABA644431022623.08BABB865541013522.86BABB16654101352.86BBAA71111777100.0BBBA23222721315.38BBBA43322721330.77BBBB1044431022343.48ABAP SQL: SELECT char1, char2,
num1,
COUNT(*) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ) AS cnt,
ROW_NUMBER( ) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ) AS rnum,
RANK( ) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ) AS rank,
DENSE_RANK( ) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ) AS schlank,
MAX( num1 ) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ) AS max,
MIN( num1 ) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ) AS min,
SUM( num1 ) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ) AS sum,
division( 100 * num1,
SUM( num1 ) OVER( PARTITION BY char1
ORDER BY char2 DESCENDING ),
2 ) AS perc
FROM demo_expressions
ORDER BY char1 DESCENDING, char2 DESCENDING
INTO TABLE @FINAL(windowed_order_descending). Result:CHAR1CHAR2NUM1CNTRNUMRANKSCHLANKMAXMINSUMPERCBBBB101111101010100.0BBBA232221021612.5BBBA433221021625.0BBAA744431022330.43BABB8211181988.89BABB1221181911.11BABA63332811540.0BAAB1044431012540.0BAAA26554101355.71BAAA866541013522.86ABBB94111912142.86ABBB1421191214.76ABBB44311912119.05ABBB74411912133.33ABBA1555291224.55ABAB48663913511.43ABAB88763913522.86ABAB1886391352.86AABB721111071741.18AABB1022111071758.82AABA1033321072737.04AAAB37443103496.12AAAB775431034914.29AAAB676431034912.24AAAB677431034912.24 Read More Technology Blog Posts by Members articles
#SAP
#SAPTechnologyblog
