In your example they would all be in the same group. You could say the group delimiter is any gap between records that is 5 minutes or more. So records would be in the same group even if the total timespan for the group is more than 5 minutes, but all gaps between individual records in the group is less than 5 minutes. No record would then be in more than 1 group.
The sensible approach would be to use a procedural language :-)
However, in SQL, you could try the following approach: SELECT a.timestamp, MAX(b.timestamp) FROM a INNER JOIN b ON a.timestamp > b.timestamp GROUP BY a.timestamp HAVING a.timestamp - MAX(b.timestamp) > '5 minutes'
This gives you every last record of a group. Join against itself again, count the number of last records that is smaller then the target row and group by that count. Probably gets nested about 4 levels deep, so I am not going to write it out ;-)
Let me stress again that I would use a procedural language and not SQL.
Jochem
--
I don't get it
immigrants don't work
and steal our jobs
- Loesje
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
