MySQL Forums
Forum List  »  Newbie

how to compare the current record to the next in the same table in terms of 'datetime'?
Posted by: Federico Lanas
Date: June 14, 2014 02:19PM

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.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.