mysql – Query to find the second highest row in a subquery

The goal is to send notifications about the customer updates but only for the first one if there are consecutive updates from the customer in a ticketing system.

This is the simplified query that I’m using to get the data that I need. There are a few more columns in the original query and this subquery for threads is kind of required so I can also identify if this is a new ticket or if existing one was updated (in case of update, the role for the latest threads will be a customer):

SELECT t.ref, m.role 
  FROM tickets t 
  LEFT JOIN threads th ON (t.id = th.ticket_id) 
  LEFT JOIN members m ON (th.member_id = m.id) 
 WHERE th.id IN ( SELECT MAX(id) 
                    FROM threads 
                   WHERE ticket_id = t.id
                )

It will return a list of tickets so the app can send notifications based on that:

+------------+----------+
| ref        | role     |
+------------+----------+
| 210117-001 | customer |
| 210117-002 | staff    |
+------------+----------+

Now, I want to send only a single notification if there a multiply consecutive updates from the customer.

Question:

How I can pull last and also one before last row to identify if this is consecutive reply from the customer?

I was thinking about GROUP_CONCAT and then parse the output in the app but tickets can have many threads so that’s not optimal and there are also a few more fields in the query so it will violate the ONLY_FULL_GROUP_BY SQL mode.

db<>fiddle here