Maximum Value in Script for different ID

[#1]

Hi all,

I have data like

Table1:

ID	Value
1	53
1	32
1	89
1	45
2	31
2	32
3	59
4	30
4	-39
5	21
5	34
5	46
6	-

I want

Table2:

ID	Value
1	89
2	32
3	59
4	-39
5	46
6	-

How to do this in Script?

Script written :-

Table2:

Load Distinct ID,

          Max(Value) as New_Value

Resident Table1;

But its showing invalid expression on reload.

Any help?

Regards,

Anjali Gupta

[PrashantSangle · Accepted solution]

Hi,

Use Group By

Regards

[PrashantSangle]

Since Max() is aggregation funtion then you have to use group by

try like

Load Distinct ID,

          Max(Value) as New_Value

Resident Table1

group by ID;

for details see in help menu

Regards,

[#4]

Thanks Max for the quick response.

Forgot about the group by clause

[Peter_Cammaert]

And leave the DISTINCT out. GROUP BY takes care of returning a single ID value.

[PrashantSangle]

Hi,

Please close thread, If you got the answer.

Regards,

[#7]

Hi I have one more question regarding this.

Can i use

Load *,

          Max(Value) as New_Value

Resident Table1

group by ID;

As i have more than one column.

Using this is showing invalid expression error again.

[tamilarasu]

You need to all the fields in group by statement. Something like below,

Load Distinct ID,

              Field1,

              Field2,

              Max(Value) as New_Value

Resident Table1

group by ID, Field1, Field2;

[Peter_Cammaert]

No you cannot. For every value of ID, the GROUP BY clause will reduce possibly a lot of records to a single one and take the maximum value for field Value and store it in New_Value. But what should it do with all other possibly different field values?

All other fields have to be specified either in the GROUP BY clause or embedded in an aggregation function (like Sum, Count, Max etc.) in the column list.

FirstSortedValue is an interesting function to pick values in other fields, based on a order you choose (like a timestamp for most recent value).

Best,

Peter

[PrashantSangle]

Hi,

for group by clause you have to add all those field which are not aggregated.

Regards