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 < 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 < '2018-04-04 00:00:00', which should return
id -- b c
I've tried adding a.date < and b.date < in the LEFT JOIN ON, tried adding a.date < to the WHERE but I'm not getting the expected results. Thanks!