CODEWITHSUNDEEP
mysqlsql
charlie
charlie

Reputation: 481

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

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
    )
)

Upvotes: 0

Views: 727

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270523

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

For this problem statement, the code would use not exists:

select t.*
from tickets t
where not exists (select 1
                  from ticket_updates tu
                  where tu.ticketnumber = t.ticketnumber and
                        tu.datetime > now() - interval 1 hour
                 );

This returns tickets that have had more than one hour since the last update.

It is unclear to me what this problem statement has to do with the code you have shown.

Upvotes: 0

Popeye
Popeye

Reputation: 35920

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

Upvotes: 1

Related Questions