Re: analyzing query results

"Lonni J Friedman" <netllama(at)gmail(dot)com> writes:
> select subtest,os,arch,info FROM ${table} WHERE (SELECT now() -
> interval '24 hours' < date_created::timestamp)='t' AND
> current_status='FAILED' ;

> My problem is that I can't think of a non-trivial way to adjust the
> SQL query so that it will only capture the last time the subtest ran,
> in the event that it ran multiple times during a 24 hour window.

If you don't mind using a nonstandard feature, DISTINCT ON will probably
help you solve this. This example looks pretty close to being the same
as the "get the latest weather reports" example that you'll find in the
PG reference page for SELECT.

BTW, why are you writing the timestamp filter condition in such a
bizarre way? I'd expect to see that query written as

select subtest,os,arch,info FROM ${table}
WHERE
(now() - interval '24 hours') < date_created
AND current_status='FAILED' ;

which would have a fighting chance of using an index on date_created.
The useless sub-SELECT not only eliminates any chance of using an index,
but incurs a fair amount of extra runtime overhead.

regards, tom lane