Skip to Content
avatar image
Former Member

HANA - Finding maximum in a row

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:

NameDate 1Date 2Date 3Date 4Date 5Max DateA14-Jan-165-Jan-166-Jan-167-Jan-168-Jan-168-Jan-16A24-Jan-165-Jan-166-Jan-167-Jan-168-Feb-168-Feb-16A34-Jan-165-Jan-166-Apr-167-Jan-168-Feb-166-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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Apr 14, 2016 at 06:18 AM

    In your SQL manual have a look at keyword GREATEST.

    regards

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 14, 2016 at 06:23 AM

    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

    Add comment
    10|10000 characters needed characters exceeded