How to get most recent field value in streamstats

[pkhimani]

I have the following query
index=qa sourcetype=xxx (JobName =xxxx) ClassName=xxxx | dedup buildNum, jobName, TestName | streamstats global=f current=f window=1 first(buildNum) as priorBuildNum, first(Status) as priorStatus by jobName, TestName | stats latest(Status) as currentStatus by buildNum,TestName |table buildNum, TestName , currentStatus

I have 1st three columns with the above query . I want to compute the LastPassedBuildNum column ( I have added expected results)
Logic would be : if currentStatus is Success --> get the buildNum and display that, if currentStatus is Error --> get the most recent buildNum when test was Success

buildNum TestName currentStatus Last PassedbuildNum
4532 TestName1 Success 4532
4532 TestName2 Error 4531 (assume)
4533 TestName1 Success 4533
4533 TestName2 Error 4531(assume)
4534 TestName1 Error 4533 (should be most recent passed buildNum)
4534 TestName2 Success 4534

Could someone help me compute the last column in above table?

I used foreach too but for some reason, i have not been able to get the desired result
Thanks!

[martin_mueller]

Copy this run-anywhere example into a new search window:

| stats count as build | eval status = "Success Error Success Success Error Error Success"
| makemv status | mvexpand status | streamstats count as build
| streamstats last(eval(case(status="Success", build))) as lastSuccessfulBuild

Results:

build  lastSuccessfulBuild  status
1      1                    Success
2      1                    Error
3      3                    Success
4      4                    Success
5      4                    Error
6      4                    Error
7      7                    Success 

This should be translatable to your problem.