Re: analyzing query results - Mailing list pgsql-novice

Lonni J Friedman wrote:
> The problem here is that occasionally the same subtest runs more than
> once in a 24 hour period (although not often), and not all of the test
> runs will have the same result (current_status).  To make matters more
> complicated when this happens, the additional runs will have a
> current_status of PASSED, yet my automated report still grabs the
> older FAILED test result, and this confuses the hell out of the people
> reviewing the report, since its technically passed already.

DDL and some sample data would be useful to help out, but if I
understood what you were describing, here's a simple mockup I tossed
into my local playground:

create table testresults (
    id              serial not null primary key,
    subtest         text not null,
    os              text not null,
    arch            text not null,
    info            text not null,
    current_status  text not null,
    date_created    date not null);

insert into testresults
    (subtest, os, arch, info, current_status, date_created)
    values
    ('a','linux','x86','info','PASSED',now()),
    ('b','linux','x86','info','PASSED',now()),
    ('c','linux','x86','info','FAILED',now()),
    ('c','linux','x86','info','FAILED','2007-07-01'),
    ('c','linux','x86','info','PASSED','2007-07-01'),
    ('d','linux','x86','info','FAILED',now()),
    ('d','linux','x86','info','PASSED',now()),
    ('e','linux','x86','info','FAILED','2007-07-01');

select a.*
from testresults a
    left join testresults b on (b.id <> a.id and
        b.date_created >= a.date_created and
        b.current_status = 'PASSED' and
        row(b.subtest, b.os, b.arch)
        = row(a.subtest, a.os, a.arch))
where a.current_status = 'FAILED' and b.id is null and
    a.date_created >= now() - interval '24 hours';

 id | subtest |  os   | arch | info | current_status | date_created
----+---------+-------+------+------+----------------+--------------
  8 | c       | linux | x86  | info | FAILED         | 2007-08-10
(1 row)

The row constructors within the join should be adjusted to match the set
of columns within the table that would adequately match two separate
rows as having been the same subtest on a different run (I just picked
the three that stood out from your original query as possibly being
identifying).

This may well not be the best way to do it. I've obviously only tested
it against a very tiny set of data. And without any DDL or sample data,
I may have misunderstood the problem.

-Jon

--
Senior Systems Developer
Media Matters for America
http://mediamatters.org/