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>)

Code

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');

Code

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;

Code

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

Code

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;

Code

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;

Code

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.