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


SQLScript Nth_Value Aggregation Function in SAP HANA


Using SQLScript Nth_Value() function, SQL developers can return the value of an element which is ordered by a specified column ascending or descending order and has a certain position in the ordered list.

For example, using NTH_VALUE function SQLScript developer can return the first row in the SAP HANA database table.

An other example can be to return the top 5 rows (where NTH_VALUE is 5 or less) when SAP HANA database table data is ordered according to a rule specified in the function arguments.

Here is the SQLScript syntax for NTH_VALUE() function for developers.

NTH_VALUE (<column1>, ORDER BY <column2>)

Above Nth_Value() function enables SQLScript developers to return the field value of column1 at position n when data is ordered by column2.

Let's make an example.
Assume we have a SAP HANA database table named Books where we store book attributes like name and price.
In below SQLScript, you will find table creation and population with sample data for this tutorial.

CREATE TABLE Books (Id int, price int, code varchar(100));

INSERT INTO Books VALUES(1, 10, 'What is SAP HANA?');
INSERT INTO Books VALUES(2, 13, 'SAP HANA Overview');
INSERT INTO Books VALUES(3, 20, 'SAP S/4HANA');
INSERT INTO Books VALUES(4, 8, 'SAP HANA Cloud Platform');
INSERT INTO Books VALUES(5, 12, 'End-to-End SAP HANA');
INSERT INTO Books VALUES(6, 30, 'SAP HANA Beginners Guide');
INSERT INTO Books VALUES(7, 10, 'SAP HANA Essentials');
INSERT INTO Books VALUES(8, 15, 'Introduction to SAP HANA');

Let's now select the book which is the first most expensive book when all books are ordered according their price.

SELECT
 NTH_VALUE(Id, 1 ORDER BY Price DESC),
 NTH_VALUE(Price, 1 ORDER BY Price DESC)
FROM Books;

The output of above SQLScript Select statement where Nth_Value() function is used for first row is as follows returning the book with price 30

SQLScript Nth Value aggregation function

And what about for the first 3 rows for example.
Or in our tutorial sample, which books are the top three expensive books stored in SAP HANA database table Books?
Here comes the answer with a SQLScript Select statement.

SELECT * FROM Books
WHERE price >= (
 SELECT NTH_VALUE (price, 3 ORDER BY price DESC) FROM Books
)
ORDER BY price DESC

As you see, the top 3 books are filtered by price column for values equal to or greater than the 3rd expensive book's price.

SQLScript for top N rows using Nth Value function

Of course, SQLScript developers are free to use more than one column in the ORDER BY list.

SELECT
 NTH_VALUE(Id, 2 ORDER BY Price ASC, Id DESC) book,
 NTH_VALUE(Price, 2 ORDER BY Price ASC, Id DESC) price
FROM Books;

This sample is interesting because I'm trying to find the book which is least expensive from the cheapest book :)
In short, the second cheapest book can be found by executing the following SQL Select statement.

SELECT
 NTH_VALUE(Id, 2 ORDER BY Price ASC) book,
 NTH_VALUE(Price, 2 ORDER BY Price ASC) price
FROM Books;

Although there are two books with the same price, 10 and which are the second cheapest books of the HANA database table, only one of them is returned by the NTH_VALUE SELECT query.

So it is better to keep in mind, SQLScript NTH_VALUE function returns only a single value even there are more than one column1 values for targeted column2 in the syntax.






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