Using the above explanation in this sample, for each ‘staff_id’, how can I compare the current row with the next (using ‘payment_date’ for current and next), so it brings only the pair of records where the amount of the current record is the same as the next (something like current.amount = next.amount). This means that each record should be compared to the next of the same ‘staff_id’, and so on.
I’m currently using this query, which do the job, but it takes for ever. I know it works good because I setted LIMIT 3 and it brought the correct ones (you can test it as well if you have the Sakila sample database):
SELECT * FROM payment a
JOIN payment b ON a.staff_id = b.staff_id AND a.payment_date > b.payment_date AND a.amount = b.amount
LEFT JOIN payment c ON a.staff_id = c.staff_id AND c.payment_date < a.payment_date AND c.payment_date > b.payment_date
WHERE c.payment_id IS NULL
Additionally, this is what 'EXPLAIN' brought in the query above: