Select Random Row Per Category using SQLScript on HANA Database
To select random rows for each category or group in a SQL database table, ABAP programmers developing code on SAP HANA database can use SQLScript Row_Number() function and Rand() function in a SELECT statement to complete this task. In a SAP HANA CDS view code or in native HANA database developments, SQL codes shared with SQLScript developers in this tutorial can help to return a number of rows either randomly or ordered categorized and grouped according to a column value in the select list.
To select random rows in HANA database, SQL programmers can refer to referenced SQLScript tutorial for selecting Random rows.
Here is our sample SQL script which returns one random product for each product category stored in SAP HANA database table "Products".
select category, productid
from (
select
row_number() over (partition by category order by rand()) as rn,
productid, category
from products
) as tbl
where rn = 1;
Here is the output of the above SQL code execution on SAP HANA database with demo tables.
HANA sample database content table "products" or with its fully-qualified name sap.hana.democontent.epm.data::MD.Products includes items from 25 different categories.
Above SQL SELECT query returns a random item for each item category (or any grouping based on a column).
In the screenshot, I only copied a part of the resultant dataset.
SQLScript developers can see that the SQL script is formed of two SQL Select statements.
The inner SELECT statement uses ROW_NUMBER() function with its PARTITION BY clause to number each item in order specified by the ORDER BY clause within that partition (or group, category, etc.)
This means, for each different category numbering of items of that category will be numbered starting from 1 in a random order specified by the RAND() function.
The outer SELECT statement filters output of the inner select with row_number column value is 1.
This results with first items from each category.
For example, if your requirement is to select 5 random items for each category, then change your SQLScript query's WHERE clause as follows:
where rn <= 5;
One last note, for random selection on HANA database table, I used SQLScript Rand() function in the ORDER BY clause of Row_Number().
You can use more realistic random functions if you need to be more precise.
Or if you need the first 3 rows (or products) for each category according to their product names (in alphabetical order), you can change the ORDER BY clause of SQL SELECT statement as following:
select category, productid
from (
select
row_number() over (partition by category order by productid) as rn,
productid, category
from products
) as tbl
where rn <= 3
order by category, productid;
