how to compare the current record to the next in the same table in terms of 'datetime'?
Hello people, I'm new here. I've been dealing with this problem for around 1 moth and I still don't get an answer, so I come to you looking for some help.
Problem: I’m going to explain this problem using the Sakila sample database and it data so it is easier for you (you can get it here:
http://dev.mysql.com/doc/sakila/en/).
Ok, so my question is how can I compare the current record to the next in the same table in terms of 'datetime'. This is how the table looks like:
payment_id customer_id staff_id rental_id amount payment_date last_update
1 1 1 76 2.99 25/05/2005 11:30:37 15/02/2006 22:12:30
2 1 1 573 0.99 28/05/2005 10:35:23 15/02/2006 22:12:30
3 1 1 1185 5.99 15/06/2005 00:54:12 15/02/2006 22:12:30
4 1 2 1422 0.99 15/06/2005 18:02:53 15/02/2006 22:12:30
5 1 2 1476 9.99 15/06/2005 21:08:46 15/02/2006 22:12:30
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
LIMIT 3;
Additionally, this is what 'EXPLAIN' brought in the query above:
# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'a', 'ALL', 'idx_fk_staff_id', NULL, NULL, NULL, '16086', NULL
'1', 'SIMPLE', 'b', 'ref', 'idx_fk_staff_id', 'idx_fk_staff_id', '1', 'sakila.a.staff_id', '4021', 'Using where'
'1', 'SIMPLE', 'c', 'ref', 'idx_fk_staff_id', 'idx_fk_staff_id', '1', 'sakila.a.staff_id', '4021', 'Using where; Not exists'
Thank you.