think. Select statements operate on a row-by-row basis, with no regard to any other rows in the dataset. Unless you can write a subselect to extract the details of the previous row - which is a possability depending on your data and query - then you might need to go for cursors instead, and I'm not sure if Mysql supports those...
Without knowing your data at all - so you might have to adapt this heavilly - something like this *might* work:
SELECT Object_Id, Event, T1, T2, DATEDIFF(T1, T2) FROM (
SELECT
Object_Id,
Event,
Timestamp AS T1,
(SELECT
Timestamp
FROM Data d2
WHERE d2.Object_Id = d1.Object_Id
AND d2.Event_Type = d1.Event_Type
AND d2.Timestamp < d1.Timestamp
ORDER BY Timestamp DESC
LIMIT 1) AS T2
FROM Data d1)
Even if that does work, cursors or similer will be much much better performance-wise if this is going to be a common thing and not just a one-off... |
