Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Use Group By
Regards
Hi,
Use Group By
Regards
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,
Thanks Max for the quick response.
Forgot about the group by clause
And leave the DISTINCT out. GROUP BY takes care of returning a single ID value.
Hi,
Please close thread, If you got the answer.
Regards,
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.
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;
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
Hi,
for group by clause you have to add all those field which are not aggregated.
Regards