SELECT from a SELECT? Or?

#1

I have a measurement where I’m continuously storing the status of services (UP / DOWN / etc).

I am trying to select the “last” ServiceStatus available, but I also want to select only the ones which are in error condition.

At the moment I am using this query:
SELECT last("ServiceStatus") FROM "ServiceStats" WHERE Time > now() - 5m AND "ServiceStatus" <> 2 GROUP BY "ServiceName"
I want for each “ServiceName” the last “ServiceStatus”, but to select only the ones who are not running properly.

The query above is not correct as it is, because at the moment if a service is down and we fix it, the query will continue to SELECT the last “ServiceStatus” record that was in failure condition, not the last “ServiceStatus” if it was in failure condition.

I think I’m trying to achieve something like this:
SELECT "ServiceStatus" FROM (
. . SELECT last("ServiceStatus") FROM "ServiceStats" WHERE Time > now() - 5m GROUP BY "ServiceName"
) WHERE "ServiceStatus" <> 2

Is this possible at all?

#2

@paulo Yup! Thats a great use of subqueries!

#3

@jackzampolin
Awesome!!! Just what I was looking for.

Sorry but for some reason I looked «everywhere» but didn’t manage to find that.
Thanks.

1 Like
#4

@paulo Its a new feature and thats pretty well hidden in the docs. Glad I could help! :blush: