Aggregation Function FIRST(), LAST()

[Former Member]

Hi Folks,

I would like to use one of the aggregation functions FIRST(), LAST() in my script but unfortunately they are not supported in HANA. Do you guys have any work around to select the first value of the right the table in a join? The scenario as below -

Table 1

Customer Name
C1
C2

Table 2

Customer	Parameter 1	Value
C1	P1	10
C1	P2	15

Preferred Output

Customer	Value
C1	10
C2

/bikas

[Former Member]

Hi Bikas,

Couple of thoughts:

a. Have you tried creating a calculated measure and within there selected the aggregation as max & min instead of sum? You might have to also check "Calculate before aggregation"

b. May be a scripted calculation view that does SELECT TOP 1 on the field with the join of the tables with "ORDER BY <VALUE_FIELD>"  . For getting last, you could use the same SELECT TOP 1 but then also use "ORDER BY <VALUE_FIELD> DESC".

Thanks,

Anooj

[Former Member]

aah just realised first() and last() aren't same as min and max - you simply want the first & last value from that measure isnt it, apologies for the previous response.

[Former Member]

Hi Anooj,

Thanks for your thoughts.

a. I can do min() and max() very well. But here I would like to pick the first value.

b. select top 1 will give me 1 row as output but as you can see, my preferred output is more than 1 rows.

I am trying to find a work around using counter function in the calc view where I can filter where counter = 1.

Keen to hear some more thoughts.

/bikas

[former_member184768]

Hi Bikas,

How about joining the table with another select statement with top 1 from the same table. It will give you all the records from the table with the join on customer.

Ravi

[Former Member]

Hi Ravi,

If I understand it correct, joining customer.Table 1 with customer.(Customer, Top 1 value from table2)?

I believe customer, top value from table 2 is not a valid one. Let me check though.

/bikas

[former_member184768]

Nope.. both from table 1

Select x.customer, x.parameter, y.value from t1 x, (select top 1 customer, value from t1 y)

where x.customer = y.customer

Regards,

Ravi

[Former Member]

Hey Ravi,

I have not tried this yet. But thinking - Isn't Y returns only 1 record wheras X has lot many. And the join will end up returning only 1 record.

/bikas

[Former Member]

Hi Bikas

   Not sure, but may be selecting by ROWID would be closer work around.Awaiting your feedback.

Thanks

Santosh Varada

[former_member184768]

Left outer join.. . We are getting into more of trial and error mode as I haven't tried this personally yet.

If you have a requirement that the first value should be available for each customer (C1, C2, C3.. etc), then this model will not work. In that case, you'll have to define what is the "first" record mean. Does it mean the min(value) or max(value) because in case of relational database order of rows do not matter much.

I am not sure if there are pseudo columns like ROWID available in HANA, but as Varada mentioned, it is worth the try.

Regards,

Ravi