HANA - Finding maximum in a row

[Former Member]

Hi Expert,

I have a requirement to get the maximum value comparing multiple columns in a row. This has to be done in a HANA Stored Procedure code. The columns are around 10 so case statement is not feasible. Please suggest an alternate solution.

Example below:

Name	Date 1	Date 2	Date 3	Date 4	Date 5	Max Date
A1	4-Jan-16	5-Jan-16	6-Jan-16	7-Jan-16	8-Jan-16	8-Jan-16
A2	4-Jan-16	5-Jan-16	6-Jan-16	7-Jan-16	8-Feb-16	8-Feb-16
A3	4-Jan-16	5-Jan-16	6-Apr-16	7-Jan-16	8-Feb-16	6-Apr-16

In the 1st row 8th Jan is the max amongst the 5 date so its displayed , similarly for the 2nd row 8th Feb while for 3rd row 6th April gets displayed.

Thanks,

Anupama

[Former Member · Accepted solution]

In your SQL manual have a look at keyword GREATEST.

regards

[chandan_praharaj]

You can make 5 projections(Alias A,B,C,D,E) as below and join them based on Name Column.

use row_num partition and find max record as below.

row_number() over (partition by NAME order by DATE desc) as ROW_NUM

1. Alias A:

Name | Date 1

Alias B:

Name | Date 2 .....

2. Join based on Name

3. Use Row_Num.

Hope this will help.

Regards,

Chandan

[chandan_praharaj]

SELECT GREATEST ('4-Jan-16', '5-Jan-16', '6-Jan-16', '8-Jan-16') "greatest" FROM DUMMY

Output:
greatest
8-Jan-16

Yes Greatest is also the best option.