How to use Max function with Where conditions

Question

I am writing code to select the maximum value from a column that does not equal two other large values. The maximum will always be the 3rd largest value. The two largest values are place holders, (int) in year month format 999912, and 999901.

I have tried using Max and Filter together with no luck.

val maxSurvey = s.max("SurveyMonth").filter(survey("SurveyMonth") =!= "999912" && survey("SurveyMonth") =!= "999901")

I expect the current result to be 201902.

try s.filter($"SurveyMonth" != ""999912" && $"SurveyMonth") != "999901").max($"SurveyMonth") — Rajan Chauhan
– Rajan Chauhan, Commented Apr 22, 2019 at 19:56

skulden · Accepted Answer · 2019-04-22 19:59:51Z

1

You need select the max, but your code is wrong in the filter too, if you need Max, why you compare SurveyMonth with a String?

After changes your code will look like:

val maxSurvey = s.filter('SurveyMonth =!= 999912 && 'SurveyMonth =!= 999901).select(max('SurveyMonth))

answered Apr 22, 2019 at 19:59

skulden

4232 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

skulden Over a year ago

you can use 'column_name when you need refer a column

Collectives™ on Stack Overflow

How to use Max function with Where conditions

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related