ROW_NUMBER( ), RANK( ) and DENSE_RANK( ) in HANA SQL Script
ROW_NUMBER ( )– Function is used to generate a sequential number and assign to each row in the result set table. Then this column can be used do perform different sorting operation on the result set.
OVER ( ) – function is used to specify which column to be considered while generating a row no. for the row of the result set.
Below is an AMDP procedure-
CLASS zcl_amdp_explore DEFINITION PUBLIC FINAL CREATE PUBLIC .
PUBLIC SECTION.
INTERFACES: if_amdp_marker_hdb.
CLASS-METHODS: get_info EXPORTING VALUE(et_spfli) TYPE spfli_tab
RAISING cx_amdp_error.
ENDCLASS.
CLASS zcl_amdp_explore IMPLEMENTATION.
METHOD get_info BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT USING spfli.
— ROW_NUMBER ( )- Assigns a ROW Number( 1,2,3,… ) to each row in the result set
— OVER( )- is used to consider which column is to be taken into account
— while assigning a number to a row in the result data set and also we can specify the order by ASC/DESC
lt_spfli1 = SELECT carrid, connid, countryfr, countryto,
ROW_NUMBER ( ) OVER( ORDER BY “CARRID” ASC ) AS “ROW_ID”
FROM SPFLI WHERE mandt = session_context( ‘CLIENT’ );
lt_spfli2 = SELECT carrid, connid, countryfr, countryto,
ROW_NUMBER ( ) OVER( ORDER BY “CARRID” DESC ) AS “ROW_ID”
FROM SPFLI WHERE mandt = session_context( ‘CLIENT’ );
— PARTITION BY: It helps to create small distinct sets within the result set and
— then applying the number to each of the set
lt_spfli3 = SELECT carrid, connid, countryfr, countryto,
ROW_NUMBER ( ) OVER( PARTITION BY “COUNTRYFR” ORDER BY “CARRID” ASC ) AS “ROW_ID”
FROM SPFLI WHERE mandt = session_context( ‘CLIENT’ );
et_spfli = SELECT * FROM spfli WHERE mandt = session_context( ‘CLIENT’ );
ENDMETHOD.
ENDCLASS.
Program that calls AMDP method.
Debug Details- Analysis of 3 result set tables.
row number is generated in ascending order of CARRID and assigned to each row.
row number is generated in descending order of CARRID and assigned to each row.
The partition is created for the country from field and this partition is considered as one of the result set and then the row no. is assigned to each of the partitioned set.
Now the result set for the first table[ lt_spfli1] is order with CARRID descending while row number is generated for each row with ascending order of CARRID.
Now the result set for the first table[ lt_spfli2] is order with CARRID ascending while row number is generated for each row with descending order of CARRID.
Debug Results- lt_spfli1
Debug Details- lt_spfli2
Til now- we know about use of ROW_NUMBER( ) function with OVER( ) and PARTITION.
Now lets’s make use of RANK( ) function-
CLASS zcl_amdp_explore DEFINITION PUBLIC FINAL CREATE PUBLIC .
PUBLIC SECTION.
INTERFACES: if_amdp_marker_hdb.
CLASS-METHODS: get_info EXPORTING VALUE(et_spfli) TYPE spfli_tab
RAISING cx_amdp_error.
ENDCLASS.
CLASS zcl_amdp_explore IMPLEMENTATION.
METHOD get_info BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT USING spfli.
— ROW_NUMBER ( )- Assigns a ROW Number( 1,2,3,… ) to each row in the result set
— RANK( ) – Assign the rank no. to each row in the result set
— and assigns duplicate values in the ranking sequence when there are ties between values
— and the next rankings are skipped.
lt_spfli1 = SELECT carrid, connid, countryfr, countryto,
ROW_NUMBER ( ) OVER( ORDER BY “CARRID” ASC ) AS “ROW_ID”,
RANK ( ) OVER( ORDER BY “CARRID” ASC ) AS “RANK_ID”
FROM SPFLI WHERE mandt = session_context( ‘CLIENT’ );
lt_spfli2 = SELECT carrid, connid, countryfr, countryto,
ROW_NUMBER ( ) OVER( PARTITION BY “COUNTRYFR” ORDER BY “CARRID” ASC ) AS “ROW_ID”,
RANK ( ) OVER( PARTITION BY “COUNTRYFR” ORDER BY “CARRID” ASC ) AS “RANK_ID”
FROM SPFLI WHERE mandt = session_context( ‘CLIENT’ );
et_spfli = SELECT * FROM spfli WHERE mandt = session_context( ‘CLIENT’ );
ENDMETHOD.
ENDCLASS.
Debug details-
LT_SPFLI1- For duplicate records( in the sense with same column value )- same rank is assigned and then the number is skipped.
LT_SPFLI1– The partition creates a small result set in the whole result set and then rank is applied to each of the row.
Let’s make use of DENSE_RANK( ) function-
CLASS zcl_amdp_explore DEFINITION PUBLIC FINAL CREATE PUBLIC .
PUBLIC SECTION.
INTERFACES: if_amdp_marker_hdb.
CLASS-METHODS: get_info EXPORTING VALUE(et_spfli) TYPE spfli_tab
RAISING cx_amdp_error.
ENDCLASS.
CLASS zcl_amdp_explore IMPLEMENTATION.
METHOD get_info BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT USING spfli.
— ROW_NUMBER ( )- Assigns a ROW Number( 1,2,3,… ) to each row in the result set
— RANK( ) – Assign the rank no. to each row in the result set
— and assigns duplicate values in the ranking sequence when there are ties between values
— and the next rankings are skipped.
— DENSE_RANK( )- function performs the same ranking operation as the RANK function,
— except that rank numbering does not skip when ties are found.
lt_spfli1 = SELECT carrid, connid, countryfr, countryto,
ROW_NUMBER ( ) OVER( ORDER BY “CARRID” ASC ) AS “ROW_ID”,
RANK ( ) OVER( ORDER BY “CARRID” ASC ) AS “RANK_ID”,
DENSE_RANK ( ) OVER( ORDER BY “CARRID” ASC ) AS “DRANK_ID”
FROM SPFLI WHERE mandt = session_context( ‘CLIENT’ );
lt_spfli2 = SELECT carrid, connid, countryfr, countryto,
ROW_NUMBER ( ) OVER( PARTITION BY “COUNTRYFR” ORDER BY “CARRID” ASC ) AS “ROW_ID”,
RANK ( ) OVER( PARTITION BY “COUNTRYFR” ORDER BY “CARRID” ASC ) AS “RANK_ID”,
DENSE_RANK ( ) OVER( PARTITION BY “COUNTRYFR” ORDER BY “CARRID” ASC ) AS “DRANK_ID”
FROM SPFLI WHERE mandt = session_context( ‘CLIENT’ );
et_spfli = SELECT * FROM spfli WHERE mandt = session_context( ‘CLIENT’ );
ENDMETHOD.
ENDCLASS.
LT_SPFLI1- in dense rank, rank no is not skipped.
LT_SPFLI1- Dense Rank use with Partition.
SAPCODES 