MySQL - Select when last entry before timestamp has specific value

Question

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!

Lennart - Slava Ukraini · Accepted Answer · 2018-04-25 17:54:27Z

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 < 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 < b.ts
      and b.ts < '2018-04-04 00:00:00'
) 
and a.name = 'last' 
and a.value = 'johns'
and a.ts < '2018-04-04 00:00:00';

RDFozz · Accepted Answer · 2018-04-25 17:59:10Z

Alternately, instead of using an anti-join to filter out rows with dates earlier than the one you want, I'd rewrite the query to simply join to the maximum date for each ID:

SELECT p.id
  FROM people p
         INNER JOIN (SELECT id, MAX(`date`) as last_date
                       FROM people
                      GROUP BY id
                    ) dt ON (p.id = dt.id AND p.`date` = dt.last_date)
 WHERE p.name = 'last'
   AND p.value = 'johns';

In this case, to limit based on the date, simply add the appropriate WHERE clause in the dt subquery:

SELECT p.id
  FROM people p
         INNER JOIN (SELECT id, MAX(`date`) as last_date
                       FROM people
                      WHERE `date` < '2018-04-04 00:00:00'
                      GROUP BY id
                    ) dt ON (p.id = dt.id AND p.`date` = dt.last_date)
 WHERE p.name = 'last'
   AND p.value = 'johns';

Here's the db-fiddle showing both versions of the query working.

I left out the DISTINCT as it was unnecessary with the data provided; if you may have multiple entries with the same id, date, name, and value, then you would want to put that back in.

Rick James · Accepted Answer · 2018-05-09 21:45:28Z

0

WHERE date < '...'
  AND name = 'last'
  AND value = 'Jones'
ORDER BY date DESC
LIMIT 1

Will get you the previous row. I'll let you build on that to do the rest of the task.

Meanwhile, you should rethink the use of EAV type schema.

answered May 9, 2018 at 21:45

Rick James

80.8k5 gold badges52 silver badges120 bronze badges

Add a comment |

Stack Exchange Network

MySQL - Select when last entry before timestamp has specific value

3 Answers 3

Your Answer

Hot Network Questions

MySQL - Select when last entry before timestamp has specific value

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions