mysql - Query where another row's datetime is over 1 hour ago

Question

I am trying to run this query against tickets table. ticket_updates table contains rows matching tickets.ticketnumber = ticket_updates.ticketnumber

I want to check for rows in tickets where the last row in ticket_updates.datetime is >= 1 hour ago.

The problem with the below is that it's picking up rows from ticket_updates where datetime is over 1 hour ago, because its in my WHERE clause, so it's completely ignoring the most recent row which in fact is only 10 minutes ago.

So I think I need to remote the datetime from my WHERE clause, but I'm not sure what to add to make it work.

SELECT * FROM tickets WHERE
(
    status = 'Pending Response' AND
    ticketnumber IN
    (
        SELECT ticketnumber FROM ticket_updates WHERE
        type = 'customer_reminder_flag' AND
        datetime < NOW() - INTERVAL 2 DAY
    )
) OR
(
    status = 'Pending Completion' AND
    ticketnumber IN (
        SELECT ticketnumber FROM ticket_updates WHERE
            type = 'update' AND
            datetime < NOW() - INTERVAL 1 HOUR
        ORDER BY datetime DESC
    )
)

Answer 1

You can re-write your query using EXISTS as follows:

SELECT t.* 
  FROM tickets t join ticket_updates tu on t.ticketnumber = tu.ticketnumber 
 WHERE t.status = 'Pending Completion' 
   AND tu.type = 'update' 
   AND tu.datetime < NOW() - INTERVAL 1 HOUR
   AND NOT EXISTS 
       (SELECT 1 FROM ticket_updates tuu
         WHERE tu.ticketnumber  = tuu.ticketnumber 
           AND tuu.type = 'update' 
           AND tuu.datetime < NOW() - INTERVAL 1 HOUR
           AND tuu.datetime > tu.datetime
       )

If you are running on mysql 8.0+ then you can use analytical function as follows:

SELECT * FROM
(SELECT t.*, row_number() over (partition by tu.ticketnumber order by tu.datetime) as rn 
  FROM tickets t join ticket_updates tu on t.ticketnumber = tu.ticketnumber 
 WHERE t.status = 'Pending Completion' 
   AND tu.type = 'update' 
   AND tu.datetime < NOW() - INTERVAL 1 HOUR) t
 WHERE RN = 1