Getting latest sensor value in set of devices

Question

I have a table with following schema:

meshSetVersion integer
gatewayMAC text
deviceMAC text
sensorID integer
sensorValue text
timestamp time

First I find the latest meshSetVersion with:

(select max(meshSetVersion) from test where gatewayMAC='XXX') as lastV

Then I find it's devices:

select distinct gatewayMAC, deviceMAC from test
where gatewayMAC='xxx' and meshSetVersion=lastV

Then I need to get latest sensorValue of each ID by timestamp

Answer 1

One method uses distinct on. I think this is the logic that you want:

select distinct on (sensorId) t.*
from test t
where t.meshSetVersion = (select max(t2.meshSetVersion)
                          from test t2
                          where t2.gatewayMach = 'xxx'
                         )
order by sensorId, timestamp desc;