$schema->resultset("Thing")->search({ ... })->prefetch('last_event')->all

##</code><code>##

SELECT me.*, thing_max_event.* FROM thing me
LEFT JOIN thing_event thing_max_event ON thing_max_event.id = (
  SELECT TOP 1 id FROM thing_event
  WHERE thing_event.thing_id = me.id
  ORDER BY timestamp DESC
)
WHERE ...

##</code><code>##

package App::Schema::ResultSet::ThingEvent;

sub last_rs {
   my $self = shift;
   
   return $self->search_rs(undef,{
      order_by => { -desc => $self->me . 'timestamp' },
      rows => 1,
      # per conversation with ribasushi, this should be safe for a single row query
      unsafe_subselect_ok => 1
   });
}

##</code><code>##

package App::Schema::Result::Thing;

__PACKAGE__->might_have( last_event => 'MyApp::Schema::Result::ThingEvent', sub {
   my ($rsrc, $us, $them)= @{$_[0]}{'self_resultsource','self_alias','foreign_alias'};
   my $guard= $rsrc->schema->set_limit_dialect_scope_guard('Top'); # use "SELECT TOP x FROM ..." syntax for subquery
   return {
      "$them.id" => { '=' =>
         $rsrc->schema->resultset('ThingEvent')->alias('thing_max_event')->search({
               'thing_max_event.thing_id' => { -ident => "$us.id" },
            })->last_rs->columns(['thing_max_event.id'])->as_query
      }
   };
});

##</code><code>##

package App::Schema;

=head2 set_limit_dialect_scope_guard

  {
    my $dialect_scope= $schema->set_limit_dialect_scope_guard('Top');
    # make DBIC searches with "SELECT TOP $n" syntax
    ...
  }
  # back to normal

By default, DBIC generates ugly nested queries with a row_id range selected in
the outer where clause.  This is actually the canonical way to do it on
SQL Server, but can create all sorts of trouble with namespace between the
different layers of the query.  Sometimes for sub-queries it is nice to have
DBIC generate "SELECT TOP 1 foo FROM bar" instead of the usual mess.  DBIC has
a C<< sql_maker->limit_dialect >> option for this, but there is no way to
constrain it to a single statement.

This "scope guard" object allows you to control the exact scope in which you
want DBIC to use the alternate dialect without worrying about exceptions.

=cut

# This scope guard object sets the "limit_dialect" of the storage engine back to whatever
# it was originally when the object goes out of scope.
{package MSSQL_LimitDialectFlipper;
   sub new {
      my ($class, $storage, $value)= @_;
      my $self= { storage => $storage, prev_value => $storage->sql_maker->limit_dialect };
      Scalar::Util::weaken($self->{storage});
      $storage->sql_maker->limit_dialect($value);
      bless $self, $class
   }
   sub DESTROY {
      $_[0]{storage}->sql_maker->limit_dialect($_[0]{prev_value}) if $_[0]{storage};
   }
}

sub set_limit_dialect_scope_guard {
   my ($self, $new_dialect_value)= @_;
   MSSQL_LimitDialectFlipper->new($self->storage, $new_dialect_value);
}

##</code><code>##

$schema->resultset("Thing")->join('last_event_of_type1','last_event_of_type2')
  ->where('last_event_of_type1.timestamp' => { '>=', $cutoff1 },
          'last_event_of_type2.timestamp' => { '<', $cutoff2 });