Max and group by

[#1]

Dear Community,

I'm trying to join a summary table that picks up the latest transaction to my main table but I get the error "invalid expression" and I'm not sure why?

LastException:
Left Join (Quote)
Load
Analysis.acode as LastException,
Analysis.quote as quote,
max(Analysis.stamp) as LastExceptionUpdateStamp
Resident Analysis
Where left(Analysis.acode,3)='EXC'
Group by Analysis.quote;

Please can you help?

Best regards

Drew

[sunny_talwar · Accepted solution]

You need all non-aggregating fields in your group by statement. (Add Analysis.acode to your group by statement)

LastException:
Left Join (Quote)
LOAD Analysis.acode as LastException,
          Analysis.quote as quote,
          Max(Analysis.stamp) as LastExceptionUpdateStamp
Resident Analysis
Where Left(Analysis.acode,3)='EXC'
Group By Analysis.quote, Analysis.acode;

[tamilarasu]

Try,

LastException:
Left Join (Quote)
Load
Analysis.acode as LastException,
Analysis.quote as quote,
max(Analysis.stamp) as LastExceptionUpdateStamp
Resident Analysis
Where left(Analysis.acode,3)='EXC'
Group by Analysis.acode, Analysis.quote;

[sunny_talwar]

or this if you don't want to aggregate by Analysis.acode, but just use it for excling stuff:

LastException:
Left Join (Quote)
LOAD Analysis.quote as quote,
          Max(Analysis.stamp) as LastExceptionUpdateStamp
Resident Analysis
Where Left(Analysis.acode,3)='EXC'
Group By Analysis.quote;

[#5]

hi,

LastException:
Left Join (Quote)
Load
Analysis.acode as LastException,
Analysis.quote as quote,
max(Analysis.stamp) as LastExceptionUpdateStamp
Resident Analysis
Where left(Analysis.acode,3)='EXC'
Group by Analysis.quote,Analysis.acode;

[jagan]

Hi,

You have to specify all the fields in Group by which you are using Select

LastException:
Left Join (Quote)
Load
Analysis.acode as LastException,
Analysis.quote as quote,
max(Analysis.stamp) as LastExceptionUpdateStamp
Resident Analysis
Where left(Analysis.acode,3)='EXC'
Group by Analysis.quote, Analysis.acode;