SQLScript Row_Number Function with Partition By Clause on SAP HANA Database

As SQL programmers developing code on SAP HANA database already knows SQLScript Row_Number() function enables developers to create an ordered series of numbers that a number showing the order of the row in that result set is assigned to that related data row. By using SQL Row_Number function, database developer can define a sorting criteria using ORDER BY clause within the ROW_NUMBER syntax and assign a number to each row.

By using PARTITION BY clause in Row_Number() syntax, SQL programmer can define numbering of rows categorized by listed columns following Partition By clause. So, executing single SELECT statement on a HANA database, multiple ordering can be managed based on categories using Partition By in SQLScript Row_Number function.

SQL Row_Number Function for Most Recent Orders

For example, if ABAP developer wants to read most recent sales order number (VBELN field) for each sales document type (categorized by AUART field) from Sales Orders table (VBAK), following SQL Select statement can be used.


with cte as (

 select

  row_number() over (partition by AUART order by VBELN desc) as rownumber,

  VBELN,

  AUART,

  ERDAT,

  NETWR,

  WAERK

 from "SAPABAP1".VBAK

)

select * from cte where rownumber = 1;

Code

ROW_NUMBER function is applied and numbers are created based on AUART field as partitioning column so that biggest (most recent) order number is 1.

When the above SQL statement is executed, you see in below output we can see only 1 in ROWNUMBER field because we applied the WHERE clause criteria. This row is the newest sales order for that specific sales document type.

SAP HANA database SQL

Maybe if we select first three orders for each AUART with Z as first letter, the partition by for SQL row_number function can be understood better.


with cte as (

 select

  row_number() over (partition by AUART order by VBELN desc) as rownumber,

  VBELN,

  AUART,

  ERDAT,

  NETWR,

  WAERK

 from "SAPABAP1".VBAK

 where AUART like 'Z%'

)

select * from cte where rownumber <= 3;

Code

You see, SQL programmer can query SAP HANA database table VBAK for most recent 3 sales orders using "row_number() over (partition by AUART order by VBELN desc)" in SELECT field list of CTE expression and applying filter on row number for less than or equal to 3 criteria.

SQL Row_Number function on HANA database

The row_number() function value is restarting for different AUART field values since AUART field is in partition by clause field list.

I hope it is now more clear for SAP HANA SQLScript database developers to use Row_Number() function with Partition By clause.