MySQL - Select when last entry before timestamp has specific value I can find rows in a table where the last row has a specific value. However, I'm having a hard time finding them when I need to limit the max, or min, timestamp.

Let's say I have table 'people'.

id   name   value   date
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


I want to get the id's where the last row has name='last' and value='johns'

SELECT  distinct a.id
FROM    people a
left JOIN people b
   ON (a.id = b.id and a.name=b.name and a.date &lt; b.date)
where b.date is null
  and a.name = 'last'
  and a.value = 'johns';


This gives me

id
--
a


Great. However, now I want to limit my search where date &lt; '2018-04-04 00:00:00', which should return

id
--
b
c


I've tried adding a.date &lt; and b.date &lt; in the LEFT JOIN ON, tried adding a.date &lt; to the WHERE but I'm not getting the expected results. Thanks!
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';