MySQL - Select when last entry before timestamp has specific value
First, always post with create table statements and insert statements. You will get much more attention if people dont have to do a lot of work to be able to help you. After all, your time is not more valuable than everyone else time:

create table people 
( id char(1) not null
, name char(4) not null
, value varchar(20) not null
, ts timestamp not null);

insert into people (id, name, value, ts)
values ('a',    'last',   'jones',   '2018-04-03 01:00:00')
     , ('a',    'last',   'smith',   '2018-04-03 03:03:03')
     , ('a',    'last',   'johns',   '2018-04-04 02:02:02')
     , ('b',    'last',   'johns',   '2018-04-03 12:12:12')
     , ('b',    'last',   'smith',   '2018-04-04 03:03:03')
     , ('c',    'last',   'smith',   '2018-04-03 11:11:11')
     , ('c',    'last',   'johns',   '2018-04-03 17:17:17')
     , ('c',    'last',   'jones',   '2018-04-05 01:01:01');


It might be easier to grasp your query if you instead of a left join uses a NOT EXISTS predicate (rows where it does not exists a row after ..., i.e. last row):

select id 
from people a 
where not exists (
    select 1 
    from people b 
    where a.id = b.id 
      and a.name=b.name 
      and a.ts &lt; b.ts
) 
and a.name = 'last' 
and a.value = 'johns';


If you are interested in the situation before a certain point in time, add that predicate to both inner and outer select:

select id 
from people a 
where not exists (
    select 1 
    from people b 
    where a.id = b.id 
      and a.name=b.name 
      and a.ts &lt; b.ts
      and b.ts &lt; '2018-04-04 00:00:00'
) 
and a.name = 'last' 
and a.value = 'johns'
and a.ts &lt; '2018-04-04 00:00:00';