How to filter for consecuting timeline events in pure SQL? I run a LPR system on a parking lot, and I record every plate along with the camera that recorded it.

I have 2 cameras, camera 1 is on the entry and camera 2 on the exit. So for every reading I record the following on a table

Id|Camera|Plate|Timestamp


I need to run a query which returns every car who has not exited the parking lot. So the condition would be: I need to return every record which has an entry (camera = 1) not followed by an exit (camera = 2).

Which would be the best approach? I don't want to use cursors if possible.

Should I create 2 temp tables:

SELECT plate, MAX(Timestamp)
FROM records WHERE camera = 1 GROUP BY plate, MAX(timestamp)


one for each camera, then

SELECT temp1_entries except temp2_exits 


and the result of the above query would be the cars who hasn't exited along with their entry timestamp.

DBMS is MariaDB, version 10.3.7.

Is this approach correct? Is there a better approach?
You don't need cursor for that.

If your id are automatically incrementing, you also don't need ordering by the timestamp, the id is enough. If not, then use timestamps. Note, timestamps have some possibility of funny effects (timezones, daylight saving times, simultaneous effects inside a single second and many similar "surprises").

This query filters those camera1 events, which are not followed by a camera2 event - either because the car is in the lot, or because they are tricking. The query handles also "funny" cases, for example, if a car would be detected by the camera on the entrance multiple times.

It does not handle the case somebody leaves the lot without entering it, but understanding the logic, it is easy to improve.

WITH Raw AS (
  SELECT RANK() OVER (PARTITION BY Plate ORDER BY Id) AS EventNo,
  Id,
  Camera,
  Plate,
  Timestamp
  FROM CameraLog
)
SELECT Prev.*
FROM CameraLog AS Prev
LEFT JOIN CameraLog AS Next
  ON Prev.Plate = Next.Plate AND Prev.EventNo + 1 = Next.EventNo
WHERE Prev.Camera = 1 AND Next.Camera &lt;&gt; 1;


The logic is that first we extend the table with the "event history" order of a single car by the RANK() windowing function: the Raw subquery essentially extends the CameraLog table with a new, EventNo column. This column is essentially an auto-incrementing id, but it is ordered for all the plates induvidually.

In the second step, in the main query, we join this table with itself together. Essentially, we try to find for all event the next one.

The result is that we have such a table:

columns of camera events | columns of the consecuting camera event


In this column structure, it is already easy to filter with a WHERE (last line).

Note, this query will be highly suboptimal, if the size of the tables is in the order of the available RAM in your server. If you need to handle large data tables quickly, you will need more advanced techniques.