on 04-14-2016 6:57 AM
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
In your SQL manual have a look at keyword GREATEST.
regards
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
86 | |
10 | |
10 | |
9 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.