Removing "duplicates" within a time range I have a table that contains number plate data with a date and time stamp, collected by a plate-reading CCTV camera at a site entrance. Vehicles stopping at the site entrance will create multiple entries of the same plate for a short period, with slightly different entry times, as in the example below:

PLATE  | Date_in   | TIME_IN
DE54RPY|2013-08-29 | 14-24-30
DE54RPY|2013-08-29 | 14-24-36
DE54RPY|2013-08-29 | 14-24-42
DE54RPY|2013-08-29 | 14-24-48


I found another solution on this site that suggested using this to filter out only the minimum timestamp for each plate:

select b.* 
from (
    select plate,date_in,MIN(time_in) time_in
    from tblin 
    group by plate,date_in
) A
inner join tblin B 
    using (plate,date_in,time_in)


The issue arises when a vehicle leaves the site and returns a while later, producing more entries in the table. These later entries will also be removed by the example solution above.

I need to find a way of filtering out a single instance (minimum or maximum) of a plate detection within a certain time period (e.g: a few minutes), and I'm not sure how to approach this. Could anybody here help me?
The easy way to make this work is to add an AUTO_INCREMENT to your transaction table and then to look for instances where the license plate value changes.  Consider this schema and query:  (you can also see it on SQL Fiddle)

This solution works by taking advantage of the fact that records are inserted in order and that a sequential number can be used to find adjacent records.  The records you're interested in are the ones where the sequence is disrupted.  In your case, that happens when the license plate value changes from one record to the next.

CREATE TABLE PLATE_VIEWS
( `SYSKEY` int not null AUTO_INCREMENT 
, `PLATE` varchar(7)
, `DATE_IN` datetime
, `TIME_IN` varchar(10)
, PRIMARY KEY (SYSKEY)
)
;

INSERT INTO PLATE_VIEWS
    (`PLATE`, `Date_in`, `TIME_IN`)
VALUES
    ('DE54RPY', '2013-08-29', '14-24-30'),
    ('DE54RPY', '2013-08-29', '14-24-36'),
    ('DE54RPY', '2013-08-29', '14-24-42'),
    ('BDDX987', '2013-08-29', '15-55-05'),
    ('DE54RPY', '2013-08-29', '17-05-33'),
    ('DE54RPY', '2013-08-29', '17-05-48')
;


Now this query gets you the first capture of each plate:

SELECT 
  A.SYSKEY 
, A.PLATE
, A.DATE_IN
, A.TIME_IN
FROM PLATE_VIEWS A
LEFT OUTER JOIN PLATE_VIEWS B
ON A.SYSKEY = B.SYSKEY + 1
WHERE A.PLATE &lt;&gt; B.PLATE
   OR B.PLATE IS NULL


If you can't change the schema of your table to add an AUTO_INCREMENT column, then start by creating a temporary table that includes the auto increment and fill it with the range of plate scans that you are interested in.  Then run the above query against the temporary table.



EDIT: In and Out

OP says there are two lanes, one for coming in and one for going out.  If each lane has a camera and both capture transactions to the same table, you can catch the situation where a car leaves and comes back without any different cars coming in the meantime.  If we change the column names to be more generic (DATE_SEEN instead of DATE_IN etc.) and add a LANE column which says "in" or "out", then you can change the query like so:

SELECT 
  A.SYSKEY 
, A.PLATE
, A.DATE_SEEN
, A.TIME_SEEN
, A.LANE
FROM PLATE_VIEWS A
LEFT OUTER JOIN PLATE_VIEWS B
ON A.SYSKEY = B.SYSKEY + 1
WHERE A.PLATE &lt;&gt; B.PLATE
   OR A.LANE &lt;&gt; B.LANE
   OR B.PLATE IS NULL


This will catch a car that leaves and comes back.  (See SQL Fiddle)

This query also shows when cars leave.  If you want to ignore when cars leave, just change the WHERE clause like so:

WHERE (A.PLATE &lt;&gt; B.PLATE
   OR A.LANE &lt;&gt; B.LANE
   OR B.PLATE IS NULL)
  AND A.LANE = 'IN'