SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SAP ABAP Programming and HANA Database Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.

CRM Companies List
Web Based CRM Software

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 (
  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.

select random row per item category on SAP HANA database query

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 (
  row_number() over (partition by category order by productid) as rn,
  productid, category
 from products
) as tbl
where rn <= 3
order by category, productid;

SAP Tutorials

SAP Tutorial

SAP Forums

SAP Tools

SAP Transaction Codes Table

Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems