GROUP BY question

[Bengt Lindholm]

I have a table where I need to group the content on a timestamp. Any 
record that is less than say 5 minutes from any other record needs to 
be grouped with that other record.

ID   timestamp
 1   2004-02-02 12:00:00
 2   2004-02-02 12:00:05
 3   2004-02-02 12:05:20
 4   2004-02-02 12:15:00
 5   2004-02-02 12:17:15
 6   2004-02-02 12:21:20
With this recordset I would get:
1  2 should be grouped since there's less than 5 minutes between the 
records
3 will be in a group of its own
4, 5  6 is a third group since any record in the group has less than 5 
minutes to another record in the group

Is this possible to do in MySQL, and how is it accomplished?



Regards,

Bengt Lindholm

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: GROUP BY question

[Bengt Lindholm]

On 2004-02-10, at 16.21, Brian Power wrote:

I'm not sure if it is possible to do with a group by

Say you had
1   2004-02-02 12:00:00
2   2004-02-02 12:00:05
3   2004-02-02 12:00:09
4   2004-02-02 12:00:12
this would require 1,2 and 3 in one group and
2,3,4 in another. My understanding is that you cant have the same rec 
in two groups


Thanks Brian,

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.

Regards,

Bengt Lindholm

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]