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
