* select x.project, x.jobset, x.job, x.system, x.id, x.timestamp, r.buildstatus, b.id, b.timestamp
  from (select project, jobset, job, system, max(id) as id from Builds where finished = 1 group by project, jobset, job, system) as a_
  natural join Builds x
  natural join BuildResultInfo r
  left join Builds b on b.id =
    (select max(id) from builds c
     natural join buildresultinfo r2
     where x.project = c.project and x.jobset = c.jobset and x.job = c.job and x.system = c.system
           and x.id > c.id and r.buildstatus != r2.buildstatus);

        })

        });

    $latest = $latest->search(
        {},
        { '+select' => ["me.statusChangeId", "me.statusChangeTime"]
        , '+as' => ["statusChangeId", "statusChangeTime"]
        , order_by => "statusChangeTime DESC"
        });

        [getJobStatus($self, $c)->search({buildstatus => {'!=' => 0}})];

        [getJobStatus($self, $c)->search({'me.buildstatus' => {'!=' => 0}})];

   my $hydradbi = getHydraDBPath ;
   if ($hydradbi =~ m/^dbi:Pg/) {
      __PACKAGE__->sequence('builds_id_seq');
  }

    my $hydradbi = getHydraDBPath;
    if ($hydradbi =~ m/^dbi:Pg/) {
        __PACKAGE__->sequence('builds_id_seq');
    }

    makeSource('JobStatus' . $name, "select * from (select project, jobset, job, system, max(id) as id from Builds where finished = 1 $constraint group by project, jobset, job, system) as a natural join Builds");
    makeSource('LatestSucceeded' . $name, "select * from (select project, jobset, job, system, max(id) as id from Builds natural join BuildResultInfo where finished = 1 and buildStatus = 0 $constraint group by project, jobset, job, system) as a natural join Builds");

    my $joinWithStatusChange =
        "natural join BuildResultInfo r " .
        "left join Builds b on b.id = " .
        "(select max(id) from builds c natural join buildresultinfo r2 " .
        "  where x.project = c.project and x.jobset = c.jobset and x.job = c.job and x.system = c.system and " .
        "    x.id > c.id and r.buildstatus != r2.buildstatus)";
    makeSource('JobStatus' . $name, "select *, b.id statusChangeId, b.timestamp statusChangeTime from (select project, jobset, job, system, max(id) as id from Builds where finished = 1 $constraint group by project, jobset, job, system) as latest natural join Builds x $joinWithStatusChange");
    makeSource('LatestSucceeded' . $name, "select * from (select project, jobset, job, system, max(id) as id from Builds natural join BuildResultInfo where finished = 1 and buildStatus = 0 $constraint group by project, jobset, job, system) as latest natural join Builds x $joinWithStatusChange");

        [% IF showStatusChange %]
          <th class="headerSortUp">Last status change</th>
        [% END %]

          [% IF showStatusChange %]
          <td>
            [% IF build.get_column('statusChangeTime') %]
              <a href="[% c.uri_for('/build' build.get_column('statusChangeId')) %]">
                [% date.format(build.get_column('statusChangeTime'), '%Y-%m-%d %H:%M:%S') %]
              </a>
            [% ELSE %]
              <em>never</em>
            [% END %]
          </td>
          [% END %]

[% INCLUDE renderBuildList builds=brokenBuilds %]

[% INCLUDE renderBuildList builds=brokenBuilds showStatusChange=1 %]

<p>Below are the latest builds for each job.</p>

<p>Below are the latest builds for each job.  It is ordered by the status
change time (the timestamp of the last build that had a different
build result status).  That is, it shows the jobs that most recently
changed from failed to successful or vice versa first.</p>

[% INCLUDE renderBuildList builds=latestBuilds %]

[% INCLUDE renderBuildList builds=latestBuilds showStatusChange=1 %]

--create index IndexBuildsByJobAndSystem on Builds(project, jobset, job, system);

create index IndexBuildsByJobAndSystem on Builds(project, jobset, job, system);
create index IndexBuildResultInfo on BuildResultInfo(id); -- primary key index, not created automatically by PostgreSQL