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.


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