InfluxDB - Query the MAX of last values for every existing tag

Here is an example of a measurement in my database :

time                category      VM       observation   severity
----                --------      ---      -----------   --------
1434067460000000000 availability  VM2      cpu           1
1434067470000000000 availability  VM2      memory        3
1434067480000000000 availability  VM1      memory        2
1434067490000000000 treacability  VM1      filesystem    1

I'd like to query the current state of a category for a given VM, the state of a category being the MAX of the last severity of all the distinct existing observations for this given category. (Eg. for availability of the VM2 here, it is the max of the severity of the last cpu observation and the last memory observation).

I can get the distinct observations for a category with a query like this one:

select distinct("observation") from (select observation, severity from my_measurement where category='availability')

But there is no WHERE IN clause like in SQL for using it as a subquery.

Any idea how to solve this with influxdb ?

Solution

I solved this with a simple GROUP BY. Example for the category availability and the virtual machine VM2:

SELECT MAX("value") FROM (
    SELECT "value"
    FROM my_measurement
    WHERE category='availability' AND VM='VM2'
    GROUP BY observation
    ORDER BY DESC
    LIMIT 1)
ORDER BY DESC