Time Variance Query works - Sorry it needs a tweak
-
So this query now works really well SELECT DISTINCT b.event_id AS a, b.user_ID AS a, b.date AS a, b.Time AS a FROM quadrantids2017 AS a JOIN quadrantids2017 AS b WHERE a.`date` = b.`date` AND a.user_ID != b.user_ID AND time_to_sec(a.`Time`) - time_to_sec(b.`Time`) BETWEEN -30 AND 30 ORDER BY a.date ASC, a.Time ASC, a.user_ID ASC I have two issues 1 - Its slow, there are 3144 records and its taking 37 seconds to get the result which is 1519 records so I added an index to event,_id, user_id, date and time and its reduced to 24 secs 2 - It returns the results like this 33501 1 2017-01-01 01:14:59 47829 3 2017-01-01 01:24:49 33503 1 2017-01-01 01:24:48 37787 2 2017-01-01 02:21:13 33504 1 2017-01-01 02:20:43 47837 3 2017-01-01 03:06:19 33505 1 2017-01-01 03:06:17 47838 3 2017-01-01 03:07:30 33506 1 2017-01-01 03:07:28 33507 1 2017-01-01 03:07:42 47840 3 2017-01-01 04:35:12 33510 1 2017-01-01 04:35:11 Im wondering how I might show the related results ie 47829 3 2017-01-01 01:24:49 33503 1 2017-01-01 01:24:48 37787 2 2017-01-01 02:21:13 33504 1 2017-01-01 02:20:43 47837 3 2017-01-01 03:06:19 33505 1 2017-01-01 03:06:17 47838 3 2017-01-01 03:07:30 33506 1 2017-01-01 03:07:28 33507 1 2017-01-01 03:07:42 47840 3 2017-01-01 04:35:12 33510 1 2017-01-01 04:35:11 Is there any way I can add a group number to each record or something like that ? John B