select tmptable.project_name, tmptable.geometry_name, tmptable.geometry_id, tmptable.diff --, tmptable.lost_for_seconds from (SELECT project_name, geometry_name, geometry_id, max(time) as time, (now()-max(time)) as diff, (extract(epoch from (now()-max(time)))) as lost_for_seconds FROM public.metrics_app_timeandgeometric as a join public.metrics_app_geometry as b on a.geometry_id = b.id where project_name in ('Lasswade', 'Mauricewood_School', 'Leconfield_House', 'Roman_Baths' )-- PROJECT_NAME -- and (geometry_name like '%_iaq' -- or geometry_name like '%_count' -- or geometry_name like '%_sound') and metric_name not in ('Occupancy', 'Utilisation', 'PlannedOccupancy', 'PlannedUtilisation', 'peopleCount', 'inCountTotal', 'outCountTotal' ) and sensor_id IS NOT NULL and geometry_name not like 'Window%' and geometry_name not like 'Desk%' --and value != 0 group by project_name, geometry_name, geometry_id --order by time desc ) as tmptable where tmptable.lost_for_seconds > 1*60*60 --HOUR*MINUTES*SECONDS --and tmptable.geometry_name not like 'Window%' order by tmptable.project_name asc, tmptable.lost_for_seconds desc