Fetch all documents from index having max(field) <= today's date

I have a SQL query as below which is very simple when its SQL however I am not getting how to convert it to elastic query,
Select * from user A where A.field = (select max(C.field) from user C where C.table = A.table and C.field <= sysdate) and A.field5 = 'A' and A.field2 = 'Y' and A.field3 = 'N' AND A.field4 IN ('A', 'B', 'C');

Any help is highly appreciated!

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.