I want to do a query which returns the newest 1 value per distinct combination of specific underlying fields (maybe also after a certain timestamp)
In the sql world I can do a query like this:
select Element, Pot, Quantity from Quantities where Id in ( select max(Id) from Quantities group by Element, Pot) order by Element, Pot where timestamp > '2020-06-01'
The effect of this would be if I had lets say this data:
Time, Element, Pot, Quantity 2020-06-02, Fe, 1, 100 2020-06-03, Fe, 1, 90 2020-06-03, Fe, 2, 85 2020-06-02, Cu, 1, 100 2020-06-03, Cu, 1, 90
I would get something like this back:
Fe, 1, 90 Fe, 2, 85 Cu, 1, 90
How would I do a similar query in ElasticSearch?